Keval Pandya Keval Pandya - 2 months ago 7
SQL Question

How to combine results of two or more SQL queries in one row?

I have a table "BIRTH_ENTRY" & "DEATH_ENTRY". Both contains ENTRY_DATE, SEX_ID fields.

SEX_ID = 1 (MALE) & SEX_ID = 2 (FEMALE)


I want to count the numbers of Male, Female & Total (Male + Female) entries done for Birth & Death GROUP & ORDER BY MONTH(ENTRY_DATE)

e.g.

|Month|Born_Male|Born_Female|Total_Born|Died_Male|Died_Female|Total_Died|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |


I've used this SQL:

SELECT
(*Query to select born Male*),
(*Query to select born Female*),
(*Query to select total born*),
(*Query to select died Male*),
(*Query to select died Female*),
(*Query to select total died*)

SELECT
(SELECT COUNT(SEX_ID) AS Expr1
FROM BIRTH_ENTRY
WHERE MONTH(ENTRY_DATE) = 1 AND (SEX_ID = 1)
GROUP BY MONTH(ENTRY_DATE)) AS Birth_Male_Count,
(SELECT COUNT(SEX_ID) AS Expr1
FROM BIRTH_ENTRY AS BIRTH_ENTRY_1
WHERE MONTH(ENTRY_DATE) = 1 AND (SEX_ID = 2)
GROUP BY MONTH(ENTRY_DATE)) AS Birth_Female_Count,
(SELECT COUNT(SEX_ID) AS Expr1
FROM BIRTH_ENTRY AS BIRTH_ENTRY_2
WHERE MONTH(ENTRY_DATE) = 1
GROUP BY MONTH(ENTRY_DATE)) AS Birth_Total_Count,
(SELECT COUNT(SEX_ID) AS Expr1
FROM DEATH_ENTRY
WHERE MONTH(ENTRY_DATE) = 1 AND (SEX_ID = 1)
GROUP BY MONTH(ENTRY_DATE)) AS Death_Male_Count,
(SELECT COUNT(SEX_ID) AS Expr1
FROM DEATH_ENTRY AS DEATH_ENTRY_1
WHERE MONTH(ENTRY_DATE) = 1 AND (SEX_ID = 2)
GROUP BY MONTH(ENTRY_DATE)) AS Death_Female_Count,
(SELECT COUNT(SEX_ID) AS Expr1
FROM DEATH_ENTRY AS DEATH_ENTRY_2
WHERE MONTH(ENTRY_DATE) = 1
GROUP BY MONTH(ENTRY_DATE)) AS Death_Total_Count


But as you know this query will return only one row. So, I've to write it 12 times for all 12 months. Well, that's not good enough.

So, please help me.

Thanks in advance!

Answer

I got this query to work using a VERY small data pool. You'll have to test it to see if it does exactly what you want.

Table format I used...

dbo.BIRTH - ENTRY (datetime), ID (int)
dbo.DEATH - ENTRY (datetime), ID (int)


;with cte (MONTH) AS (

    SELECT 1
    UNION ALL
    SELECT MONTH + 1 FROM cte WHERE MONTH < 12
)
SELECT c.MONTH
       , COUNT(CASE WHEN b.ID = 1 THEN 1 END) AS MALEBIRTH
       , COUNT(CASE WHEN b.ID = 2 THEN 1 END) AS FEMALEBIRTH
       , COUNT(CASE WHEN b.ID = 1 OR b.ID = 2 THEN 1 END) AS TOTALBIRTH
       , (SELECT COUNT(CASE WHEN d.ID = 1 THEN 1 END) FROM DEATH d WHERE DATEPART(mm, d.ENTRY) = c.MONTH) AS MALEDEATH
       , (SELECT COUNT(CASE WHEN d.ID = 2 THEN 2 END) FROM DEATH d WHERE DATEPART(mm, d.ENTRY) = c.MONTH) AS FEMALEDEATH
       , (SELECT COUNT(CASE WHEN d.ID = 1 OR d.ID = 2 THEN 2 END) FROM DEATH d WHERE DATEPART(mm, d.ENTRY) = c.MONTH) AS TOTALDEATH
FROM BIRTH b FULL OUTER JOIN cte c ON DATEPART(mm, b.ENTRY) = c.MONTH 
     GROUP BY c.MONTH

This displayed every month (whether or not there was ANYTHING on it). It also displays all the information you wanted.