CODEWITHSUNDEEP
sqlmysqlselectgroup-by
jcubic
jcubic

Reputation: 66590

Select with two counts on same column

I have two select queries:

SELECT MONTH(date) AS month, count(DISTINCT ip) 
FROM table_name WHERE field = 1 GROUP BY month

and

SELECT MONTH(date) AS month, count(DISTINCT ip) 
FROM table_name WHERE field = 2 GROUP BY month

how can I write one query to select

SELECT MONTH(date) AS month, 
       count(DISTINCT ip) [ for field = 1], 
       count(DISTINCT ip) [ for field = 2]

Upvotes: 1

Views: 3157

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

SELECT MONTH(date) AS month, 
       count(DISTINCT case when field = 1 then ip end) as f1,
       count(DISTINCT case when field = 2 then ip end) as f2
FROM table_name 
WHERE field in (1,2)
GROUP BY month

Upvotes: 6

Related Questions