Counting Number of Males/females within a blood group

So I have two Data Tables ‘DONOR’ AND ‘ACCEPTOR’. Each have the following schema:

DID|NAME|GENDER|CITY|BG |AMOUNT
INT|STR |CHAR  |STR |STR|INT

I’m trying to create a query to output the Gender and Blood Group (BG) as well as a Count for that Gender+Blood Group to do this I created the following query which is kind of correct but I only want it to output each Gender+Blood Group once with the total count which i don’t exactly understand how to do (I’m learning SQL and I figured out how to get this query and union it but not make it so each Gender + Blood Group only outputs once)

My Query:

SELECT DISTINCT GENDER,BG,COUNT(BG) COUNTVALUE FROM DONOR GROUP BY GENDER,BG
UNION ALL
SELECT DISTINCT GENDER,BG,COUNT(BG) COUNTVALUE FROM ACCEPTOR GROUP BY GENDER,BG

Example Output:

F A+ 30
F AB+ 75
M A- 47
M AB+ 36
M 0+ 53
. . .

My Output:

F A+ 69 
F A- 49 
F AB+ 62 
F AB- 61 
F B+ 67 
F B- 63 
F O+ 45 
F O- 59 
M A+ 55 
M A- 71 
M AB+ 76 
M AB- 63 
M B+ 59 
M B- 68 
M O+ 67 
M O- 66 
F A+ 51 
F A- 57 
F AB+ 71 
F AB- 62 
F B+ 60 
F B- 70 
F O+ 52 
F O- 69 
M A+ 70 
M A- 60 
M AB+ 71 
M AB- 68 
M B+ 66 
M B- 53 
M O+ 52 
M O- 68

Answer

To get what you want, you will have to use a subquery:

SELECT t.GENDER, t.BG, SUM(t.COUNTVALUE) TOTAL
FROM (
    SELECT GENDER,BG,COUNT(BG) COUNTVALUE FROM DONOR GROUP BY GENDER,BG
    UNION ALL
    SELECT GENDER,BG,COUNT(BG) COUNTVALUE FROM ACCEPTOR GROUP BY GENDER,BG
) AS t
GROUP BY t.GENDER, t.BG

Note I removed DISTINCT because it was superfluous. The GROUP BY is already sure to return only one row per distinct GENDER, BG pair in each query.

Advertisement

Answer