CODEWITHSUNDEEP
mysql
jim
jim

Reputation: 23

How to combine these two select statements

I would like to combine these 2 statements but can't seem to get it right.

I need 3 total columns. One for total, month and losses.

Here is what I have so far

SELECT Count(patient1.patient_id) AS total FROM patient AS total;
SELECT Count(patient2.patient_id) AS losses, patient2.mo AS `month`
FROM patient AS patient2
WHERE patient1.rx_exp BETWEEN '2010-10-01' AND '2010-11-01';

EDIT I need to have all three columns on a single row.

Upvotes: 2

Views: 179

Answers (1)

Martin Smith
Martin Smith

Reputation: 452988

SELECT 
    Count(patient_id) AS total,
    Count(case when rx_exp >= '2010-10-01' 
               AND rx_exp < '2010-11-01' then 1 end) AS losses,
    'October' AS `month`
FROM patient;

You could use this for the month but there's probably no point.

MAX(case when rx_exp >= '2010-10-01' 
     AND rx_exp < '2010-11-01' then patient2.mo end) AS `month`

Upvotes: 1

Related Questions