Learning SQL, trying to figure out how to get a gender count-CodePudding

I'm learning SQL and one of the questions that got thrown at me in my solo study suddenly is to write a script in order to count how many of each gender is in the client table, Table CLIENT:

ClientID `First Name` `Last Name` Gender ID `Birth Date`

1         Sally        Fields     1          03/05/2000

2         Hannah       Montana    1          02/20/1989

3         Bart         Simpson    2          02/03/1966

4         Michael      Jordan     2          05/02/1970

5         Glennon      Doyle      1          NULL

6         Lulu         Jenkins    1          09/30/2001

Table GENDER:

ID `Gender Description`

1   Female

2   Male

I'm still very new to this so I'm stumped... the furthest I've gotten is:

SELECT
u.GenderID     Gender,
COUNT(u.GenderID)

I'm not sure of where to go from there... the stuff I'm studying went from 1-100 really fast and I'm totally lost lol. Any guidance would be much appreciated!

CodePudding user response：

Assuming you want to return the gender name and a count of each, simply join your two tables and aggregate, grouping by the gender description and counting the rows that belong to each grouping:

select g.`Gender Description`, Count(*) GenderCount
from client c
join gender g on g.id = c.GenderId
group by g.`Gender Description`;

Note the use of short meaningful aliases makes the query more compact and easy to read at a glance.

CodePudding user response：

with GenderTable AS 
(
 SELECT 
 CASE 
   WHEN 'Gender ID'=1 Then  'Female' 
   Else 'Male' 
 END AS gender 
 FROM  CLIENT
)
SELECT count(gender), gender
FROM GenderTable
GROUP BY gender
;

You can try this too using the Common Table Expression