CODEWITHSUNDEEP
sqlpostgresql
Uebanchik
Uebanchik

Reputation: 25

Duplicate values when queried with generate_series and left join?

I'm trying to count data from different tables on certain dates, but for some reason, I get the wrong result, it just duplicates the count from another table.

If you look, then, in the table "items", only 1 entry. But, when counting through count (), I get 5.

Can you explain and help me solve this problem?

Data:

create table views(id bigint, created_at timestamp);
create table items(id bigint, created_at timestamp);

insert into views(id, created_at) values
('1', '2018-12-28 22:46:35'),
('2', '2018-12-28 22:46:35'),
('3', '2018-12-28 22:46:35'),
('4', '2018-12-28 22:46:35'),
('5', '2018-12-28 22:46:35');

insert into items(id, created_at) values
('1', '2018-12-28 22:46:35');

Query:

select 
dates.d as day,
count(v.*) as views_count,
count(i.*) as items_count

from (
    select d from generate_series('2018-12-01'::date, '2018-12-30', '1 day' ) as d
) as dates

left join views as v on v.created_at::date = dates.d
left join items as i on i.created_at::date = dates.d

group by day order by day desc;

DbFiddle.

Upvotes: 1

Views: 594

Answers (3)

klin
klin

Reputation: 121804

Place the aggregates in subqueries, e.g:

select 
    dates.d as day,
    coalesce(v.count, 0) as views_count,
    coalesce(i.count, 0) as items_count
from generate_series('2018-12-26'::date, '2018-12-30', '1 day' ) as dates(d)
left join (
    select created_at::date as day, count(*)
    from views 
    group by day
    ) as v on v.day = dates.d
left join (
    select created_at::date as day, count(*)
    from items 
    group by day
    ) as i on i.day = dates.d
order by day desc

          day           | views_count | items_count 
------------------------+-------------+-------------
 2018-12-30 00:00:00+01 |           0 |           0
 2018-12-29 00:00:00+01 |           0 |           0
 2018-12-28 00:00:00+01 |           5 |           1
 2018-12-27 00:00:00+01 |           0 |           0
 2018-12-26 00:00:00+01 |           0 |           0
(5 rows)

Upvotes: 0

Rasoul Taheri
Rasoul Taheri

Reputation: 822

its because your views table. you have an inner select that return each day of 2012/12. then you do left join on views table that have 5 record that all of those are in day 2012/12/28. so for this day you have 5 record.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

count(<expression>) counts the number of rows where <expression> is not NULL.

I think you want to count the number of distinct values. If so:

select dates.d as day,
       count(distinct v.id) as views_count,
       count(distinct i.id) as items_count

You can also use v.* and i.* as the argument, but the id should be sufficient.

Upvotes: 1

Related Questions