none
Calculating remaining amount of Spots left in Football Team, Using query. RRS feed

  • Question

  • Hi, i'm trying to find out how to create a query to show the amount of spots that are remaining in a football team. First off there is 300 spots i can only make my query show how many spots have already been filled and can't seem to show the remaining. Hoping someone can shed some light on this for me.

    To show the spots filled already i am using 2 fields from 2 tables and using the Totals feature to count the amount of spots filled.

    Thanks
    Tuesday, February 7, 2017 5:31 PM

All replies

  • What is the SQL statement of the query which returns the number of filled spots?

    Ken Sheridan, Stafford, England

    Tuesday, February 7, 2017 5:54 PM
  • Looks like this:

    SELECT Team.Spot, Sum(Applicant.Number) AS SumOfNumber
    FROM Team INNER JOIN [Applicant] ON Team.PlayerID = Team.PlayerID
    GROUP BY Team.Spot
    ORDER BY Team.Spot, Sum(Applicant.Number);

    Thanks.

    Wednesday, February 8, 2017 2:24 AM
  • Hi Big Dobbie 420,

    why you are matching the Team.PlayerID=Team.PlayerID.

    its from the same table.

    you need to select the playerid from the other table.

    like Team.PlayerID=Applicant.PlayerID.

    example query

    SELECT CategoryName, ProductName
    FROM Categories INNER JOIN Products
    ON Categories.CategoryID = Products.CategoryID;

    Reference:

    INNER JOIN Operation

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 8, 2017 5:11 AM
    Moderator
  • SELECT Team.Spot, Sum(Applicant.Number) AS SumOfNumber
    FROM Team INNER JOIN [Applicant] ON Team.PlayerID = Team.PlayerID
    GROUP BY Team.Spot
    ORDER BY Team.Spot, Sum(Applicant.Number);

    As Deepak points out that doesn't make a lot of sense.  I would have expected there to be a one-to-many relationship type between teams and applicants, with the Applicant table including a TeamID foreign key column referencing the primary key TeamID column of the Team table.

    Can you post full details of the two table definitions, listing all columns from each and indicating the columns on which the tables are related.



    Ken Sheridan, Stafford, England

    Wednesday, February 8, 2017 12:17 PM