CODEWITHSUNDEEP
mysqlsql
sic
sic

Reputation: 13

Merging two select statements

I need to merge 2 select results, one is like this:

SELECT count(emaildata2.EM_SENT_FLAG), emaildata2.EMAIL_FINANCIAL_WEEK 
FROM `email-redeye`.emaildata2
WHERE emaildata2.EM_SENT_FLAG='Yes'
GROUP BY emaildata2.EMAIL_FINANCIAL_WEEK

and the other like this:

SELECT count(emaildata2.EM_OPEN_FLAG), emaildata2.EMAIL_FINANCIAL_WEEK 
FROM `email-redeye`.emaildata2
WHERE emaildata2.EM_OPEN_FLAG='Yes'
GROUP BY emaildata2.EMAIL_FINANCIAL_WEEK

so that the output looks like this:

count(opens)|count(sends)|Week
 2               8        52
 5              15        53

I have tried various selects, unions but the results of the count always rolls up to a total and is not broken down by the week.

any ideas?

Upvotes: 1

Views: 60

Answers (3)

Tom Collins
Tom Collins

Reputation: 4069

Doing this from my phone, so it's a very short answer.

Make your second query a sub query of your first.

If you're not familiar with sub queries, it's basically a whole query in place of a table name. They are very useful.

Upvotes: 0

ngrashia
ngrashia

Reputation: 9894

You can use SUM WITH CASE as below:

Syntax :

CASE WHEN CONDITION THEN TRUE_VALUE ELSE FALSE_VALUE END

SELECT 
 SUM(CASE WHEN emaildata2.EM_SENT_FLAG = 'Yes' THEN 1 ELSE 0 END) SEND_COUNT,
 SUM(CASE WHEN emaildata2.EM_OPEN_FLAG= 'Yes' THEN 1 ELSE 0 END) OPEN_COUNT,
 emaildata2.EMAIL_FINANCIAL_WEEK
FROM `email-redeye`.emaildata2
GROUP BY emaildata2.EMAIL_FINANCIAL_WEEK

You can also use SUM WITH IF as below:

Syntax :

IF(CONDITION) , TRUE_VALUE , FALSE_VALUE

SELECT 
  SUM(IF(emaildata2.EM_SENT_FLAG='Yes', 1, 0) SEND_COUNT, 
  SUM(IF(emaildata2.EM_OPEN_FLAG='Yes', 1, 0)) OPEN_COUNT,
  emaildata2.EMAIL_FINANCIAL_WEEK 
FROM `email-redeye`.emaildata2
GROUP BY emaildata2.EMAIL_FINANCIAL_WEEK

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Just add the extra count but use IF and SUM:-

SELECT SUM(IF(emaildata2.EM_SENT_FLAG='Yes', 1, 0), SUM(IF(emaildata2.EM_OPEN_FLAG, 1, 0)), emaildata2.EMAIL_FINANCIAL_WEEK 
FROM `email-redeye`.emaildata2
GROUP BY emaildata2.EMAIL_FINANCIAL_WEEK

Upvotes: 1

Related Questions