CODEWITHSUNDEEP
sqlsql-server
Amr Badawy
Amr Badawy

Reputation: 7673

What's the best practise to write SQL to get data as follow?

I have the following two table in SQL server 2005 DB
2- "UserInfomration" ---> ( ID (auto) , Name , Gender ( bit ) )
1- "Competitors" ---> ( Id (auto) , UserID ( FK from UserInformation.ID ) , Status ( tinyint)
i want to make a stored procedure that return data as follow
Status - Male count - Female Count
     1                  45                    15
     2                 10                    50
     3                 20                    60

So could anyone share with me the best solution to do that
My Solution was the following :

SELECT  
        Competitors.Status, 
        COUNT(Competitors.ID) AS MaleCount , 
        (
            SELECT Count(C.ID) 
            FROM Competitors as C , UserInfomration as UI
            WHERE UI.UserID = C.UserID  AND UI.UserGender = 0 AND C.Status = Competitors.Status  
        ) as FemaleCount ,
        (MaleCount + FemaleCount) as total
FROM     
        UserInfomration LEFT OUTER JOIN
               Competitors ON UserInfomration.UserID = ISEFCompetitors.UserID
GROUP BY Competitors.Status, UserInfomration.UserGender
HAVING  (UserInfomration.UserGender = 1)
ORDER BY Competitors.Status

Thanks in advance for your expected cooperation

Upvotes: 0

Views: 68

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166346

You can try something like this

SELECT  c.Status,
        SUM(CASE WHEN u.Gender = 0 THEN 1 ELSE 0 END) Male, --assuming 0 is male
        SUM(CASE WHEN u.Gender = 1 THEN 1 ELSE 0 END) Female --assuming 1 is female
FROm    Competitors c INNER JOIN
        UserInfomration u ON c.UserID = u.ID
GROUP BY c.Status

Upvotes: 1

Related Questions