2 COUNT queries multiplying results

Question

I am trying to execute two COUNT statements across 3 joins. The first Count shows the correct number but the second one seems to multiply the counts together for some reason? I checked the link which was marked as duplicate but that example doesn't have any JOINS in it.

SELECT
    COUNT(DISTINCT `outlet_id`) AS `outlets`,
    `prod_name`,
    COUNT(`purchased`) AS `vouchersleft`
FROM
    `prod_outlets` AS `po`
INNER JOIN `bb_products` AS `bbp` ON po.`product_id` = bbp.`prod_id`
INNER JOIN `vouchers` AS `v` ON v.`product_id` = bbp.`prod_id`
GROUP BY
    bbp.`prod_id`;

What it should display is 3 branches and 5 vouchers. But it is outputting 3 branches and 15 vouchers. So, the second COUNT is multiplying by the first i.e.: 3 x 5 = 15

Possible duplicate of [How to get multiple counts with one SQL query?](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Daerik, May 31 '18 at 19:32
The first count should count how many branches a product is available at and the second count should count how many vouchers are available for the product — user8463989, May 31 '18 at 19:40
@user8463989 This whole thing is open to interpretation. Are you looking for the `SUM()` of purchased or are you just looking for the `COUNT()`? — Daerik, May 31 '18 at 19:43
I just want the COUNT. Eg: The product is available at 3 branches and overall there are 50 vouchers available for this product. The vouchers are not specific to any branch — user8463989, May 31 '18 at 19:43
It is just that COUNT(purchased) will return the count of all rows found, irregardless of it's value. — Daerik, May 31 '18 at 19:52
I don't really understand what you mean. I will take my first result as an example. What it should display is 3 branches and 5 vouchers. But it is outputting 3 branches and 15 vouchers. So, the second COUNT is multiplying by the first i.e.: 3 x 5 = 15 — user8463989, May 31 '18 at 19:54
If you do a SELECT * from your query, it will return 15 rows. Does it not? — Daerik, May 31 '18 at 19:57

score 1 · Accepted Answer · answered Jun 01 '18 at 06:13

From the description what i understood is you are getting cross product that is why you are getting wrong number for vouchersleft, what i suggest you to calculate your count in a sun clause and then join this clause with your main query like

SELECT
    COUNT(DISTINCT `outlet_id`) AS `outlets`,
    `prod_name`,
    v.vouchersleft
FROM
    `prod_outlets` AS `po`
INNER JOIN `bb_products` AS `bbp` ON po.`product_id` = bbp.`prod_id`
INNER JOIN (
    SELECT product_id, COUNT(*) vouchersleft
    FROM vouchers
    GROUP BY product_id
) AS `v` ON v.`product_id` = bbp.`prod_id`
GROUP BY
    bbp.`prod_id`;

score 0 · Answer 2 · answered May 31 '18 at 19:27

0

 "SELECT COUNT(DISTINCT `outlet_id`) as `outlets`,
 `prod_name`,
 COUNT(distinct `purchased`) as `vouchersleft`
 FROM `prod_outlets` as `po`
 INNER JOIN `bb_products` as `bbp`
 ON po.`product_id` = bbp.`prod_id`
 INNER JOIN `vouchers` as `v`
 ON v.`product_id` = bbp.`prod_id`
 GROUP BY bbp.`prod_id`";

answered May 31 '18 at 19:27

Daniel Marcus

2,686
1
7
13

Try using distinct on second count as well – Daniel Marcus May 31 '18 at 19:27
I already tried that and I end up with the incorrect values. Everything for the second COUNT has a value of 1 – user8463989 May 31 '18 at 19:30
Youll have to provide sample data and expected results - looks like there is a cartesian join somewhere thats messing you up – Daniel Marcus May 31 '18 at 19:31
The first query should count how many branches a product is available at and the second count should count how many vouchers are available for the product – user8463989 May 31 '18 at 19:34

2 COUNT queries multiplying results

2 Answers2