CODEWITHSUNDEEP
mysql
user3299633
user3299633

Reputation: 3380

Convert query to handle empty set results

My problem is I'm running a query where there is sometimes an empty set of results (nothing is returned). I need to modify the output so that hours with no data are still represented with the appropriate "0" count. The following documentation lists the query I'm using as well as sample output.

Current query being used:

select date(look) as "Day",concat(date_format(look, '%H'),':00') as "Hour", count(distinct(session_name)) as "Count"
from db.login_stats 
where look > '2015-10-06' 
group by date(look),concat(date_format(look, '%H'),':00');

Current Output:

+------------+-------+-------+
| Day        | Hour  | Count |
+------------+-------+-------+
| 2015-10-07 | 09:00 |     1 |
| 2015-10-07 | 10:00 |     1 |
| 2015-10-07 | 11:00 |     1 |
| 2015-10-07 | 12:00 |     2 |
| 2015-10-07 | 13:00 |     2 |
| 2015-10-07 | 14:00 |     2 |
| 2015-10-07 | 15:00 |     1 |
| 2015-10-07 | 18:00 |     1 |

What I would like the output to resemeble:

+------------+-------+-------+
| Day        | Hour  | Count |
+------------+-------+-------+
| 2015-10-07 | 00:00 |     0 |
| 2015-10-07 | 01:00 |     0 |
| 2015-10-07 | 02:00 |     0 |
| 2015-10-07 | 03:00 |     0 |
| 2015-10-07 | 04:00 |     0 |
| 2015-10-07 | 05:00 |     0 |
| 2015-10-07 | 06:00 |     0 |
| 2015-10-07 | 07:00 |     0 |
| 2015-10-07 | 08:00 |     0 |
| 2015-10-07 | 09:00 |     1 |
| 2015-10-07 | 10:00 |     1 |
| 2015-10-07 | 11:00 |     1 |
| 2015-10-07 | 12:00 |     2 |
| 2015-10-07 | 13:00 |     2 |
| 2015-10-07 | 14:00 |     2 |
| 2015-10-07 | 15:00 |     1 |
| 2015-10-07 | 16:00 |     0 |
| 2015-10-07 | 17:00 |     0 |
| 2015-10-07 | 18:00 |     1 |
| 2015-10-07 | 19:00 |     0 |
| 2015-10-07 | 20:00 |     0 |
| 2015-10-07 | 21:00 |     0 |
| 2015-10-07 | 22:00 |     0 |
| 2015-10-07 | 23:00 |     0 |

Any suggestions on how to accomplish this?

Upvotes: 0

Views: 56

Answers (1)

Ishamael
Ishamael

Reputation: 12795

There's no trivial way to do that that I know of, but it is achievable. The following query will print all 24 hours for each day that matches your WHERE clause (for instance, it will print the desired output for your current data, and if you insert an extra row with look equal to 2015-10-08 it will add 24 more rows to the output):

-- populate a table with 24 rows, one per hour

create table hours(hour time);
insert into hours values(0), (10000), (20000);
insert into hours select hour + 30000 from hours;
insert into hours select hour + 60000 from hours;
insert into hours select hour + 120000 from hours;

-- now this query will do what you want

select dates.d as "Day",dates.h as "Hour", count(distinct(session_name)) as "Count"
from db.login_stats RIGHT JOIN
    (
    SELECT date(look) as d, concat(date_format(hour, '%H'),':00') as h
    FROM login_stats CROSS JOIN hours
    WHERE date(look) > '2015-10-06' -- moved your WHERE here
    GROUP BY 1, 2
    ORDER BY 1, 2
    ) dates
    ON date(look) = d AND concat(date_format(look, '%H'),':00') = h
group by dates.d, dates.h;

Upvotes: 1

Related Questions