MYSQL count unique rows even if count is zero, for single table

Question

I have a table just like in this SQLfiddle - http://www.sqlfiddle.com/#!9/3b6f8a/1

And i try to count unique occurrences of visits, but I need to also have dates where count is zero. In sample result you can see that for date 2019-07-24 there is missing 0 result for .bid and similar for 25th.

I have searched through stackoverflow and found only solutions for multiple tables using LEFT JOIN. I have no idea how to apply it to single table.

Below is working query I have that doesnt show zero values.

SELECT COUNT(DISTINCT `hash`) wizyt, DATE(timestamp) AS timestamp, CASE
                    WHEN url LIKE '%.bid%' THEN 'bid'
                    WHEN url LIKE '%.online%' THEN 'online'
                END AS landing
            FROM `tracking_actions`
            WHERE `action` = 'w90|landing-visit'
            GROUP BY DATE(timestamp), landing  ORDER BY timestamp

Kindly please help :)

If we give you this, can you figure out the rest: `SELECT DISTINCT DATE(x.timestamp) dt , y.url FROM tracking_actions x , tracking_actions y` (the DISTINCT isn't required in the final solution) — Strawberry, Jul 26 '19 at 16:46

Vilx- · Accepted Answer · 2019-07-26T20:51:37.290

The thing is - in SQL you cannot normally create rows out of nothing. If you have a set of rows then you can reduce it in various ways (like group by or where or having), or you can multiply it with another set of rows (with various kinds of join). But you cannot create rows out of thin air, which is what you're trying to do. If there's a date with no activity, then you'll not have any rows for that and no way to get a row with that date.

So there are conceptually two ways to try to get around this. The more universal one is to create a calendar table with all the dates. Or maybe three tables years, months and days which you join together to get actual dates (though that gets a bit tricky). And then, when you have a row for each date, you can join your actual data to that, and you'll get your zeroes.

Alternatively, you need to look in DB-specific extensions to SQL.

MariaDB has common table expressions which can do this (see this related question for inspiration: How do I make a row generator in MySQL?)

It also has a "sequence storage engine" which creates pseudo-tables with numerical sequences. Those can be converted to dates.

For MySQL itself I don't know what could be used. Dig through the documentation, perhaps you'll find something that generates rows and can adapt that.

Last but not least, there's also the option to NOT use SQL for this. After getting the incomplete data set, process it in your programming language of choice (which you're probably doing anyway) and fill the gaps.

@toHo - I found the documentation for the [sequence storage engine](https://mariadb.com/kb/en/library/sequence-storage-engine/) in MariaDB and updated the answer. — Vilx-, Jul 26 '19 at 20:51

score 0 · Answer 2 · answered Jul 26 '19 at 16:54

You should first using cross join for build the keys (date and landi) for the rows you need and the using left join obatin the relateed count

select a.my_date, a.landing , ifnull(t.wizyt,0) wizyt
from (
    SELECT distinct  DATE(timestamp) my_date
    from  FROM `tracking_actions` 
    cross  join  (
        select  distinct case 
            WHEN url LIKE '%.bid%' THEN 'bid'
            WHEN url LIKE '%.online%' THEN 'online' 
            end landing
        from 
        FROM `tracking_actions`
        WHERE `action` = 'w90|landing-visit'
    ) 
    WHERE `action` = 'w90|landing-visit' 
) a 

LEFT JOIN  (

    SELECT COUNT(DISTINCT `hash`) wizyt, DATE(timestamp) AS timestamp, CASE
                        WHEN url LIKE '%.bid%' THEN 'bid'
                        WHEN url LIKE '%.online%' THEN 'online'
                    END AS landing 
                FROM `tracking_actions`
                WHERE `action` = 'w90|landing-visit'
                GROUP BY DATE(timestamp), landing  ORDER BY timestamp

) t  ON t.landing = a.landing and a.my_date  = t.timestamp

it gives and error :( MySQL server version for the right syntax to use near 'FROM `tracking_actions` cross join ( select distinct case ' at line 4 — toHo, Jul 26 '19 at 17:10

MYSQL count unique rows even if count is zero, for single table

2 Answers2