SQL count occurrences without counting duplicates in another column

Question

I have a table:

Date       | ID | Company | Click 
-----------+----+---------+--------
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Other   | 1
01/01/2021 | 02 | Us      | 0 
01/01/2021 | 02 | Other   | 0
02/01/2021 | 03 | Us      | 1 
02/01/2021 | 03 | Us      | 1 
02/01/2021 | 04 | Us      | 0

I want to group by date and count: how many different IDs there are per day, how many unique IDs have clicked=1 and Company="Us"

My current code is:

create table grouped as 
select date
, count(distinct ID) as ID_count
, sum(case when company="Us" and clicked=1 then 1 else 0 end) as Click_count
from have 
group by 1

The result should look like:

Date       | ID_count | Click_count
-----------+----------+------------
01/01/2021 | 2        | 1
02/01/2021 | 2        | 1

You'll notice that my code counts duplicated ID's, so the click_count column takes the value 2 in both dates. How can I fix that?

Actually, your query returns `Click_count = 1`: http://sqlfiddle.com/#!15/ef22e8/10 (executed on PostgreSQL). Please don't use `group by 1`. Use `group by date` instead. — cornuz, Nov 30 '21 at 15:37

score 1 · Answer 1 · answered Nov 30 '21 at 15:17

1

You should use COUNT() to count the distinct IDs with a CASE expression:

COUNT(DISTINCT CASE WHEN company = 'Us' AND clicked = 1 THEN ID END) AS click_count

answered Nov 30 '21 at 15:17

forpas

160,666
10
38
76

Stefanov.sm · Answer 2 · 2021-11-30T15:50:20.573

PostgreSQL solution using filter conditional aggregation:

select date, 
       count(distinct id) id_count,
       count(distinct id) filter (where click = 1 and company = 'Us') click_count 
from the_table
group by date;

If your database lacks the conditional aggregation filter capability then a scalar subquery - an alternative of @forpas suggestion - will do.

select date, 
       count(distinct id) id_count,
       (
         select count(distinct id) 
         from the_table
         where click = 1 and company = 'Us' and date = t.date
       ) click_count 
from the_table t
group by date;

SQL Fiddle

SQL count occurrences without counting duplicates in another column

2 Answers2