CODEWITHSUNDEEP
mysqlsqldate
Ethan
Ethan

Reputation: 146

MySQL Date Range Multi Column Count and Group By Select Statement

I have a query I have been working on for a while but I cannot seem to get it down. The other answers on here work well for counting an amount with a certain date range then grouping by the date to get the count. However I need to have two columns counted and grouped by date.

For example here is the query I have tried to get to work:

(SELECT COUNT(*) arrived, DATE(arrived) date, 'arrived' AS source
FROM products 
WHERE arrived BETWEEN '2016-01-01' AND '2016-01-31'
GROUP BY DATE(date)
ORDER BY date ASC)

UNION ALL 

(SELECT COUNT(*) released, DATE(released) date, 'released' AS source
FROM products 
WHERE released BETWEEN '2016-01-01' AND '2016-01-31'
GROUP BY DATE(date)
ORDER BY date ASC)

However this returns the following:

arrived  date       source
3        2016-01-12  arrived
2        2016-01-28  arrived
1        2016-01-29  arrived
1        2016-01-05  released

What I am requiring is something like this:

date           arrived   released   
2016-01-05     0         1
2016-01-12     3         0
2016-01-28     2         0
2016-01-29     1         0

Any suggestions? Thank you.

Upvotes: 3

Views: 951

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can apply conditional aggregation to a derived table obtained by a UNION ALL operation for 'arrived' and 'released' dates:

SELECT `date`, 
       COUNT(CASE WHEN type = 'arrived' THEN 1 END) AS arrived,
       COUNT(CASE WHEN type = 'released' THEN 1 END) AS released
FROM (
  SELECT arrived AS `date`, 'arrived' as type
  FROM products
  WHERE arrived BETWEEN '2016-01-01' AND '2016-01-31'

  UNION ALL

  SELECT released AS `date`, 'released' as type
  FROM products
  WHERE released BETWEEN '2016-01-01' AND '2016-01-31') AS t
GROUP BY `date`

Demo here

Upvotes: 3

Related Questions