CODEWITHSUNDEEP
sqlsql-server-2012
Winter Soldier
Winter Soldier

Reputation: 23

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!

Upvotes: 1

Views: 77

Answers (1)

CodyMR
CodyMR

Reputation: 415

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.

Upvotes: 1

Related Questions