CODEWITHSUNDEEP
mysql
Kaspar
Kaspar

Reputation: 103

MySQL: Count two date columns and group by day

I need to draw a line chart that will visualize both the orders and pickups for each day between certain dates. The order and pickup dates are stored in unixtime. My table looks something like this:

id    order_date    pickup_date
-------------------------------
1     1472749664     1472133376
2     1472551372     1472567548
3     1472652545     1472901368
4     1473154659     1473512323
5     1473512923     1475229824
6     1475586643     1475652635

What I am after is something like this

date        orders    pickups
-------------------------------
01-09-2016  1         0
02-09-2016  4         1
03-09-2016  3         2
04-09-2016  7         1
05-09-2016  0         0
06-09-2016  1         1
07-09-2016  6         3
08-09-2016  0         0
08-09-2016  3         5
10-09-2016  2         4

I know I can count based on one column, for example:

SELECT
    COUNT(id) AS orders,
    FROM_UNIXTIME(order_dates, '%d-%m-%Y') AS date
FROM orders
GROUP BY request_date

But I not sure how to count two columns and group them for each day.

Upvotes: 1

Views: 620

Answers (1)

moni_dragu
moni_dragu

Reputation: 1163

You could use a query like this:

SELECT sum(orders) as orders, sum(pickups) as pickups, date
FROM (
SELECT
    COUNT(id) AS orders, 0 as pickups,
    FROM_UNIXTIME(`order_date`, '%d-%m-%Y') AS date
FROM orders
GROUP BY order_date
UNION
SELECT
    0 AS orders, COUNT(id) as pickups,
    FROM_UNIXTIME(`pickup_date`, '%d-%m-%Y') AS date
FROM orders
GROUP BY pickup_date ) ut
GROUP BY date

Here is a fiddle.

Upvotes: 1

Related Questions