How to fix this MySQL query

Question

Currently I have a system where a PHP script logs some information (time and "sid") of a visit when the page is loaded.

I then use:

SELECT *
FROM (
    SELECT COUNT(*), time
    FROM visit
    WHERE sid = $sid
    GROUP BY time
    ORDER BY time DESC
    LIMIT 14
) AS abc
ORDER BY time ASC

to get the results from the last fortnight in reverse order.

The problem with this query is that on days with no visits I do not get the result: 0, [time], but instead nothing. What would I do to get a result of zero on those days? Is it even possible in MySQL or would I have to process it with PHP?

Any help greatly appreciated!

You probably need `Union` in this case.... – MD Sayem Ahmed Nov 03 '11 at 04:14 — MD Sayem Ahmed, Nov 03 '11 at 04:14

score 2 · Accepted Answer · answered Nov 03 '11 at 04:27

You have to use a second table containing all dates (or use a subquery like below). So extend the subquery for table t till CURDATE()-13 for having last 14 days.

SELECT COUNT(*),t.time FROM (
  SELECT CURDATE() AS time
  UNION
  SELECT CURDATE()-1 AS time
  UNION
  SELECT CURDATE()-2 AS time
  UNION
  SELECT CURDATE()-3 AS time
  UNION
  SELECT CURDATE()-4 AS time
  UNION
  [...]
) AS t
LEFT JOIN visit AS v ON t.time=v.time AND v.sid=$sid
ORDER BY t.time DESC

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

First of all if I remember correctly ORDER in a subquery is useless.

If I was using PostgreSQL I'd use generate_series() and outer join to get 0s for missing dates. Unfortunately MySQL has no such concept. I'd suggest to use temporary table filled with dates

edited May 23 '17 at 12:23

Community

1
1

answered Nov 03 '11 at 04:20

mlt

1,516
1
21
48

`ORDER BY` in a subquery is useful - in fact: wanted - when there is `LIMIT`. – ypercubeᵀᴹ Dec 23 '15 at 15:58

How to fix this MySQL query

2 Answers2