CODEWITHSUNDEEP
mysqlwhere-clause
UgoL
UgoL

Reputation: 909

Mysql count result in "where" clause

I'm facing a little problem with mysql where clause. This is the query:

SELECT u.id user
     , p.id product_purchased
     , p.name product_name
     , pl.store_id store
     , COUNT(*) occurrences
     , total_spent
     , total_product_purchased
     , pl.registration 
  FROM purchases_log pl 
  JOIN user u 
    ON pl.user_id = u.id 
  JOIN product p 
    ON pl.product_id = p.id
  JOIN 
     ( SELECT user_id
            , SUM(price) total_spent
            , COUNT(product_id) total_product_purchased 
         FROM purchases_log pl 
        GROUP 
           BY user_id
     ) t1 
    ON u.id = t1.user_id
 WHERE pl.store_id IN (1,2,3) 
   AND occurrences > 1 
 GROUP 
    BY user
     , product_name
 ORDER 
    BY u.id ASC
     , pl.registration ASC;

This is the output error:

Error Code: 1054. Unknown column 'occurrences' in 'where clause' 0.067 sec

I have already tried assign AS to occurrences or using pl. So, can someone explain me how to correctly define the result of a count function in where clause?

Upvotes: 0

Views: 51

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You need to use HAVING instead of COUNT as group by is applied after WHERE clause and hence, it won't know about any group/aggregate columns, e.g/:

SELECT u.id user,p.id product_purchased, p.name product_name,  pl.store_id store, COUNT(*) AS occurrences, total_spent, total_product_purchased, pl.registration 
FROM purchases_log pl 
JOIN user u ON pl.user_id=u.id 
JOIN product p ON pl.product_id=p.id
JOIN (SELECT user_id, SUM(price) AS total_spent,COUNT(product_id) AS total_product_purchased FROM purchases_log pl GROUP BY user_id) t1 ON u.id=t1.user_id
WHERE pl.store_id IN (1,2,3) 
GROUP BY user, product_name
HAVING COUNT(*) > 1
ORDER BY u.id ASC, pl.registration ASC;

Update

  • If a user has more than one product associated then it's good to add all the non aggregate columns in GROUP BY to get all the combinations of user and product. The current query will not return all the combinations.
  • For further optimization, as @strawberry has suggest, you can run EXPLAIN and see which indices are used and whether there is any need to create any new index.

Upvotes: 2

Related Questions