SQL - How to use more COUNT on many tables?

Question

I need to get the COUNT of each id_prevadzka IN 4 tables:

First I tried:

SELECT
    p.id_prevadzka,
    COUNT(pv.id_prevadzka) AS `vytoce_pocet`,
    COUNT(pn.id_prevadzka) AS `navstevy_pocet`,
    COUNT(pa.id_prevadzka) AS `akcie_pocet`,
    COUNT(ps.id_prevadzka) AS `servis_pocet`
FROM shop_prevadzky p
LEFT JOIN shop_prevadzky_vytoce pv ON (pv.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_navstevy pn ON (pn.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_akcie pa ON (pa.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_servis ps ON (ps.id_prevadzka = p.id_prevadzka)
GROUP BY p.id_prevadzka

But this returned the same number for vytoce_pocet, navstevy_pocet, akcie_pocet and servis_pocet - and it was the number, what was the COUNT in shop_prevadzky_vytoce.

Then I tried (as is answered here):

SELECT
    p.*,
    SUM(CASE WHEN pv.id_prevadzka IS NOT NULL THEN 1 ELSE 0 END) AS `vytoce_pocet`,
    SUM(CASE WHEN pn.id_prevadzka IS NOT NULL THEN 1 ELSE 0 END) AS `navstevy_pocet`,
    SUM(CASE WHEN pa.id_prevadzka IS NOT NULL THEN 1 ELSE 0 END) AS `akcie_pocet`,
    SUM(CASE WHEN ps.id_prevadzka IS NOT NULL THEN 1 ELSE 0 END) AS `servis_pocet`
FROM shop_prevadzky p
LEFT JOIN shop_prevadzky_vytoce pv ON (pv.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_navstevy pn ON (pn.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_akcie pa ON (pa.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_servis ps ON (ps.id_prevadzka = p.id_prevadzka)
WHERE p.cis_status = 1
GROUP BY p.id_prevadzka
ORDER BY p.prevadzka_nazov

But it returned the same results as is in the first example.

Whats wrong in my second query? Thanks.

EDIT:

To understand well, this could be test data in my tables:

shop_prevadzky:

id_prevadzka
1
2

shop_prevadzky_vytoce:

id | id_prevadzka
1  | 1
2  | 1
3  | 1
4  | 1
5  | 2

shop_prevadzky_navstevy:

id | id_prevadzka
1  | 1
2  | 1

shop_prevadzky_akcie:

id | id_prevadzka
1  | 2

shop_prevadzky_servis:

id | id_prevadzka

And the query should return:

id_prevadzka | vytoce_pocet | navstevy_pocet | akcie_pocet | servis_pocet
1              4              2                0             0
2              1              0                1             0

It's hard to say without seeing some sample data. I can say that your inclusion of `p.*` could prove to be very problematic here. Perhaps narrow that down to the fields from `p` that you actually need and include them in your `GROUP BY` statement if they aren't being aggregated by a formula otherwise you are going to get oddball results. Can you include some sample data for the tables so we can better understand the schema and also include your expected results form the query. — JNevill, Mar 08 '16 at 14:48
I also guessed and added the `MySQL` tag since it's the only DBMS that would allow you to exclude non-aggregated fields in your GROUP BY (which may be what's leading to the issues you are facing). Any other DBMS would throw an error if you tried to submit this SQL. — JNevill, Mar 08 '16 at 14:49
@JNevill Sorry, it takes more time, now I added simple example data with expecting result. — Legionar, Mar 08 '16 at 14:50
Thanks, @Legionar. I saw that a minute after I threw that comment out there. Thank you! It looks like Juan Carlos is all over it below. — JNevill, Mar 08 '16 at 14:51
Your query is fine,I think you are confused about what you want — Mihai, Mar 08 '16 at 15:01

Juan Carlos Oropeza · Answer 1 · 2016-03-08T15:08:05.077

That is because you are joining all tables and created a cartesian product

shop_prevadzky x _vytoce x _navstevy x _akcie x _servis

You may want

SELECT
    p.*,
    (SELECT COUNT(id_prevadzka) FROM shop_prevadzky_vytoce s WHERE s.id_prevadzka = p.id_prevadzka) AS `vytoce_pocet`,
    (SELECT COUNT(id_prevadzka) FROM shop_prevadzky_navstevy s WHERE s.id_prevadzka = p.id_prevadzka) AS `navstevy_pocet`,
    (SELECT COUNT(id_prevadzka) FROM shop_prevadzky_akcie s WHERE s.id_prevadzka = p.id_prevadzka) AS `akcie_pocet`,
    (SELECT COUNT(id_prevadzka) FROM shop_prevadzky_servis s WHERE s.id_prevadzka = p.id_prevadzka) AS `servis_pocet`
FROM shop_prevadzky p

Also you can do the same with subquerys

SELECT
    p.*,
    COALESCE(vytoce_count, 0) as vytoce_count,
    COALESCE(navstevy_count, 0) as navstevy_count,
    COALESCE(akcie_count, 0) as akcie_count,
    COALESCE(servis_count, 0) as servis_count
FROM shop_prevadzky p
LEFT JOIN  (SELECT id_prevadzka, COUNT(id_prevadzka) vytoce_count
            FROM shop_prevadzky_vytoce s 
            WHERE s.id_prevadzka = p.id_prevadzka) AS vytoce
       ON p.id_prevadzka = vytoce.id_prevadzka

LEFT JOIN  (SELECT id_prevadzka, COUNT(id_prevadzka) navstevy_count
            FROM shop_prevadzky_navstevy s 
            WHERE s.id_prevadzka = p.id_prevadzka) AS navstevy
       ON p.id_prevadzka = navstevy.id_prevadzka

LEFT JOIN  (SELECT id_prevadzka, COUNT(id_prevadzka) akcie_count
            FROM shop_prevadzky_akcie s 
            WHERE s.id_prevadzka = p.id_prevadzka) AS akcie
       ON p.id_prevadzka = akcie.id_prevadzka

LEFT JOIN  (SELECT id_prevadzka, COUNT(id_prevadzka) servis_count
            FROM shop_prevadzky_servis s 
            WHERE s.id_prevadzka = p.id_prevadzka) AS servis
       ON p.id_prevadzka = servis.id_prevadzka

Yes, thats true. Could it be done with one SELECT without using subqueries? — Legionar, Mar 08 '16 at 14:52

score 2 · Accepted Answer · edited Mar 08 '16 at 15:30

2

Try count distinct as in:

Using your data sample

SQL Fiddle Demo

SELECT
    p.id_prevadzka,
    COUNT(distinct pv.id) AS `vytoce_pocet`,
    COUNT(distinct pn.id) AS `navstevy_pocet`,
    COUNT(distinct pa.id) AS `akcie_pocet`,
    COUNT(distinct ps.id) AS `servis_pocet`
FROM shop_prevadzky p
LEFT JOIN shop_prevadzky_vytoce pv ON (pv.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_navstevy pn ON (pn.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_akcie pa ON (pa.id_prevadzka = p.id_prevadzka)
LEFT JOIN shop_prevadzky_servis ps ON (ps.id_prevadzka = p.id_prevadzka)
GROUP BY p.id_prevadzka

OUTPUT

| id_prevadzka | vytoce_pocet | navstevy_pocet | akcie_pocet | servis_pocet |
|--------------|--------------|----------------|-------------|--------------|
|            1 |            4 |              2 |           0 |            0 |
|            2 |            1 |              0 |           1 |            0 |

edited Mar 08 '16 at 15:30

Juan Carlos Oropeza

47,252
12
78
118

answered Mar 08 '16 at 14:52

John

3,458
4
33
54

@Jhon because if same `id` appeear twice you want `count = 2` – Juan Carlos Oropeza Mar 08 '16 at 15:24
Sorry, I'm assuming that the id is a primary key in this case. This should work if you are counting the primary key (which you should be doing). I've edited the above to reflect this. – John Mar 08 '16 at 15:25
I try to create a fiddle but server isnt workin right now to test it. http://sqlfiddle.com/#!9/9f63cd/1 – Juan Carlos Oropeza Mar 08 '16 at 15:27
Lol, is running again, you can check your query result now :) – Juan Carlos Oropeza Mar 08 '16 at 15:27
Sorry for the big edit. You have the right approach. – Juan Carlos Oropeza Mar 08 '16 at 15:30
Thanks for the clarification Juan Carlos! – John Mar 08 '16 at 19:18

score 0 · Answer 3 · answered Mar 08 '16 at 14:53

What about this way:

SELECT
p.id_prevadzka,
(Select COUNT(pv.id_prevadzka) from shop_prevadzky_vytoce pv where pv.id_prevadzka = p.id_prevadzka) AS `vytoce_pocet`,
(Select COUNT(pn.id_prevadzka) from shop_prevadzky_navstevy pn where pn.id_prevadzka = p.id_prevadzka) AS `navstevy_pocet`,
(Select COUNT(pa.id_prevadzka) from shop_prevadzky_akcie pa Where pa.id_prevadzka = p.id_prevadzka) AS `akcie_pocet`,
(Select COUNT(ps.id_prevadzka) from shop_prevadzky_servis ps Where ps.id_prevadzka = p.id_prevadzka) AS `servis_pocet`
FROM shop_prevadzky p
WHERE p.cis_status = 1

score 0 · Answer 4 · answered Mar 08 '16 at 15:01

0

Do you really need to do this as a single query? Why not something like:

select count(*) from foo;
select count(*) from bar;

OR

select 'FOO_COUNT' as table_name, count(*) as cnt from foo
union all 
select 'BAR_COUNT' as table_name, count(*) as cnt from bar
;

answered Mar 08 '16 at 15:01

John

3,458
4
33
54

Because it need to be GROUP BY id_prevadzka. I don't need COUNT of all rows in all tables. – Legionar Mar 09 '16 at 09:27

SQL - How to use more COUNT on many tables?

4 Answers4