CODEWITHSUNDEEP
mysqlcountgroup-byreturn
user1703914
user1703914

Reputation:

return zeros with group by and count in mysql

I am counting the number of distinct customers and grouping it by another column .I would like the result to return zeros if no customers are found in the group. With the following code, I don't get zeros in my results. Can someone please help?

SELECT download_date,resolution,COUNT(DISTINCT customer_id) FROM test.sample
GROUP BY download_date,resolution;

What this gives me is the following:

----------------------------------------
| 2011-06-09| HD       | 1             |
----------------------------------------
| 2011-06-09| SD       | 1             |
----------------------------------------
| 2012-06-10| SD       | 1             |
----------------------------------------

Whereas I am looking to get the following:

----------------------------------------
| 2011-06-09| HD       | 1             |
----------------------------------------
| 2011-06-09| SD       | 1             |
----------------------------------------
| 2012-06-10| HD       | 1             |
----------------------------------------
| 2012-06-10| SD       | 0             |
----------------------------------------

Here is the table structure :

CREATE TABLE test.sample (
`customer_id` VARCHAR(15) NULL DEFAULT NULL,
`download_date` DATE NULL DEFAULT NULL,
`resolution` VARCHAR(2) NULL DEFAULT NULL,
`total_units` INT(11) NULL DEFAULT NULL);

INSERT INTO test.sample(`customer_id`,`download_date`,`resolution`,`total_units`)
VALUES('1','2012-06-10','HD',40),('1','2012-06-10','HD',20),
('2','2011-06-09','SD',10),('2','2011-06-09','HD',20);

Upvotes: 1

Views: 132

Answers (1)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select dr.download_date, r.resolution,COUNT(DISTINCT customer_id)
from
(select DISTINCT download_date from sample) dr
cross join
(select distinct resolution from sample) r
left join sample s on s.download_date = dr.download_date
and r.resolution = s.resolution
group by dr.download_date, r.resolution;

FIDDLE

Upvotes: 1

Related Questions