SQL Query for pivot and calculation.

Am trying to write a sql query to get the data in a particular format. Please see my actual data below and the result am trying to get.

Sport            Team                No of Females                No of males

Baseball                    Varsity                             20                                                 0
Baseball                     Varsity                                                                                 20
Football                    Varsity                                                                                   15

Above is the format of the table and data in the actual database.
Now i want to get the data in this below format.

                                  Female Only                 Male only                   Total
Sports                           1                                    2                                  2
Teams                           1                                    2                                 3
Participants                20                                 35                                 55

Please help me with the sql query , I tried using pivot and count of the columns but i couldn't achieve the way i wanted. Thanks for your help!.
Elizabeth GAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Something like below, I think.  I'm allowing for the table to be able to have non-sports rows as well (Sport = '').  If that's not the case, you won't need the test for Sports or perhaps for Teams.

SELECT
    oa1.Category AS [ ],
    SUM(CASE WHEN [No of Females] > 0 AND [No of Males] = 0
             THEN CASE WHEN oa1.Category = 'Participants' THEN [No of Females] ELSE 1 END ELSE 0 END) AS [Female Only],
    SUM(CASE WHEN [No of Females] = 0 AND [No of Males] > 0
             THEN CASE WHEN oa1.Category = 'Participants' THEN [No of Males] ELSE 1 END ELSE 0 END) AS [Male Only],
    SUM(CASE WHEN ([No of Females] > 0 AND [No of Males] = 0) OR([No of Females] = 0 AND [No of Males] > 0)
             THEN CASE WHEN oa1.Category = 'Participants'
                       THEN [No of Females] + [No of Males] ELSE 1 END ELSE 0 END) AS [Total]
FROM dbo.table_name
OUTER APPLY (
    SELECT 'Sports' AS Category
    WHERE Sport > ''
    UNION ALL
    SELECT 'Teams'
    WHERE Teams > ''
    UNION ALL
    SELECT 'Participants'
    WHERE Sport > '' AND Teams > ''
) AS oa1(Category)
GROUP BY oa1.Category
0
 
Elizabeth GAuthor Commented:
Thank you so much !. I got it to work with the help of your Query.
0
 
Scott PletcherSenior DBACommented:
You're welcome!  (Btw, this q of yours put me over 10M point mark, Thanks!)
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.