SQL count with left join

Question

i want to get data from 2 tables using left join, here's my table

First table (folder)

number	date
123/123	2021-08-14
321/321	2021-08-15
456/456	2021-08-15
098/098	2021-08-16
654/654	2021-08-17

second table (certificate)

code	folder_number
asd1	123/123
asd2	123/123
asd3	123/123
asd4	123/123
asd5	123/123
qwe1	321/321
qwe2	321/321
qwe3	321/321
zxc1	456/456
zxc2	456/456
zxc3	456/456
rty1	098/098
fgh1	654/654

i only use date column from folder table, i want to count all data by date from folder table and left join certificate table that also count all code that connected/related to number column from folder table, here's my code

SELECT b.date, COUNT(c.code) as code, COUNT(b.date) as datecount
                FROM folder b
                INNER JOIN certificate c
                    ON c.folder_number = b.number
            GROUP BY b.date
            ORDER BY b.date

my expectation for that code:

date	code	datecount
2021-08-14	5	1
2021-08-15	6	2
2021-08-16	1	1
2021-08-17	1	1

but, here's what i get when use that code:

date	code	datecount
2021-08-14	5	5
2021-08-15	6	6
2021-08-16	1	1
2021-08-17	1	1

how to fix it? i also try using inner join but the result is same

Thanks, sorry for my bad english btw

forpas · Accepted Answer · 2021-08-17 06:18:41Z

3

Instead of COUNT(b.date) you should count the distinct number of number:

SELECT b.date, 
       COUNT(c.code) as code, 
       COUNT(DISTINCT b.number) as datecount
FROM folder b INNER JOIN certificate c
ON c.folder_number = b.number
GROUP BY b.date
ORDER BY b.date;

If there are numbers in folder that may have no match in certificate then you should use a LEFT join.

See the demo.

edited Aug 17, 2021 at 6:18

answered Aug 17, 2021 at 6:09

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL count with left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related