CODEWITHSUNDEEP
mysqlselectcount
Brandon
Brandon

Reputation: 27

MYSQL query select count fields

select *, count(*) as total
from customer_data
where category = 'Fashion'
group by customer_id
order by total desc

From this result of query how could i take fields where total > 5?

Upvotes: 0

Views: 71

Answers (1)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

You need a HAVING clause. Also try not to select nonaggregated columns or columns that are not in GROUP BY clause. 

SELECT aa.*, _aa.total
FROM (
    SELECT customer_id, COUNT(*) AS total
    FROM customer_data
    WHERE category = 'Fashion'
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) AS _aa
INNER JOIN customer_data AS aa
ON _aa.customer_id = aa.customer_id
ORDER BY _aa.total DESC

Have a look here and here.

Upvotes: 1

Related Questions