CODEWITHSUNDEEP
mysqlsql
user8463989
user8463989

Reputation: 2459

Unknown column error when using two COUNT statements in one query

I am trying to run 2 different occurrences of COUNT in one SQL query but am getting this error:

Column not found: 1054 Unknown column 'po.product_id' in 'on clause'

Here is the sql:

SELECT COUNT(*) vouchersleft, `prod_name`,
        `start_date`, 
        `end_date`,
        `reg_price`,
        `sale_price`,
        bbp.`prod_id`,
        `vouch_limits`,
        `sp_name`,
        `order_id`,
        `img_name`,
        `outlets`
        FROM `vouchers` as `v`
        INNER JOIN `bb_products` as `bbp` ON bbp.`prod_id` = v.`product_id`
        INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
        INNER JOIN `prod_pics` as `pp` ON pp.`prod_id` = bbp.`prod_id`
        INNER JOIN (SELECT COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`
        WHERE `frontend` = :frontend
        AND bbp.`featured` = :featured
        AND `order_id` = :order_id
        AND pp.`featured` = :featured_img
        GROUP BY bbp.`prod_id`

Upvotes: 0

Views: 417

Answers (3)

Mihai
Mihai

Reputation: 26784

Why not just join directly

SELECT COUNT(*) vouchersleft, `prod_name`,
        `start_date`, 
        `end_date`,
        `reg_price`,
        `sale_price`,
        bbp.`prod_id`,
        `vouch_limits`,
        `sp_name`,
        `order_id`,
        `img_name`,
         COUNT(DISTINCT po.`outlet_id`) AS `outlets`
        FROM `vouchers` as `v`
        INNER JOIN `bb_products` as `bbp` ON bbp.`prod_id` = v.`product_id`
        INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
        INNER JOIN `prod_pics` as `pp` ON pp.`prod_id` = bbp.`prod_id`
        RIGHT JOIN `prod_outlets`  as `po` ON po.`product_id` = bbp.`prod_id`
        WHERE `frontend` = :frontend
        AND bbp.`featured` = :featured
        AND `order_id` = :order_id
        AND pp.`featured` = :featured_img
        GROUP BY bbp.`prod_id`

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

If you want to join by the column it has to be returned by your SELECT statement. Include product_id in column list that you're selecting.

Change:

INNER JOIN (SELECT COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`

To:

INNER JOIN (SELECT `product_id`, COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`

Without the column selected, product_id is not visible outside of this intermediate table (here named po). This is why you get an error unknown column, because only known column is outlets.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

Including the column in the GROUP BY is not enough. You need to also include it in the SELECT:

SELECT product_id, COUNT(DISTINCT `outlet_id`) AS `outlets`
FROM `prod_outlets`
GROUP BY `product_id`

Upvotes: 1

Related Questions