Professional Web Applications Themes

How to... Query?? - Microsoft SQL / MS SQL Server

Hi, i need some help designing a query. let's say i got 2 tables from a Soccer Tournament DB, TEAMS PLAYERS TeamID---------------I PlayerID TeamName I----------------TeamID Etc..... Age Position How can i do a query that returns the Name of Each Team, and also in the same row, the count of players on that team that are Goalkeepeers and are 20 years or younger , and another col with the count of players who are defenders and are over 30 years. EX: TeamName GK<20 DEF>30 Team1 5 8 Team2 4 1 Team3 1 5 Team4 2 0 Thanks. Alex....

  1. #1

    Default How to... Query??

    Hi, i need some help designing a query.

    let's say i got 2 tables from a Soccer Tournament DB,

    TEAMS PLAYERS
    TeamID---------------I PlayerID
    TeamName I----------------TeamID
    Etc..... Age
    Position

    How can i do a query that returns the Name of Each Team, and also in the
    same row, the count of players on that team that are Goalkeepeers and are 20
    years or younger , and another col with the count of players who are
    defenders and are over 30 years.
    EX:

    TeamName GK<20 DEF>30
    Team1 5 8
    Team2 4 1
    Team3 1 5
    Team4 2 0

    Thanks.
    Alex.


    Ale Guest

  2. #2

    Default Re: How to... Query??

    Try:

    select
    t.TeamName
    , sum (case when p.Age < 20 and p.Position = 'GK' then 1 else 0 end) as 'GK<20'
    , sum (case when p.Age > 30 and p.Position = 'DEF' then 1 else 0 end) as 'DEF>30'
    from
    Teams as t
    left join
    Players as p on p.TeamID = t.TeamID
    group by
    t.TeamName


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ale K." <com> wrote in message news:phx.gbl...
    Hi, i need some help designing a query.

    let's say i got 2 tables from a Soccer Tournament DB,

    TEAMS PLAYERS
    TeamID---------------I PlayerID
    TeamName I----------------TeamID
    Etc..... Age
    Position

    How can i do a query that returns the Name of Each Team, and also in the
    same row, the count of players on that team that are Goalkeepeers and are 20
    years or younger , and another col with the count of players who are
    defenders and are over 30 years.
    EX:

    TeamName GK<20 DEF>30
    Team1 5 8
    Team2 4 1
    Team3 1 5
    Team4 2 0

    Thanks.
    Alex.



    Tom Guest

  3. #3

    Default Re: How to... Query??

    It Works.
    Thanks.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Try:

    select
    t.TeamName
    , sum (case when p.Age < 20 and p.Position = 'GK' then 1 else 0 end) as 'GK<20'
    , sum (case when p.Age > 30 and p.Position = 'DEF' then 1 else 0 end) as 'DEF>30'
    from
    Teams as t
    left join
    Players as p on p.TeamID = t.TeamID
    group by
    t.TeamName


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ale K." <com> wrote in message news:phx.gbl...
    Hi, i need some help designing a query.

    let's say i got 2 tables from a Soccer Tournament DB,

    TEAMS PLAYERS
    TeamID---------------I PlayerID
    TeamName I----------------TeamID
    Etc..... Age
    Position

    How can i do a query that returns the Name of Each Team, and also in the
    same row, the count of players on that team that are Goalkeepeers and are 20
    years or younger , and another col with the count of players who are
    defenders and are over 30 years.
    EX:

    TeamName GK<20 DEF>30
    Team1 5 8
    Team2 4 1
    Team3 1 5
    Team4 2 0

    Thanks.
    Alex.


    Ale Guest

Similar Threads

  1. Query of Queries with constructed query using QueryNew()and QueryAddRow()
    By LeadFoot in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 4th, 02:00 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139