CODEWITHSUNDEEP
mysqlgroup-by
user1621308
user1621308

Reputation: 172

Filtering out results obtained by using GROUP BY in MySQL

I have this query:

SELECT name, SUM(p.column1+p.column2+p.colum3) AS PICKS
FROM table AS p
WHERE date = '2014-09-10'
GROUP BY name

This query works perfect. Would I would like is to filter out results where p.column1+p.column2+p.column3 = 0

I have tried:

SELECT name, SUM(p.column1+p.column2+p.colum3) AS PICKS
FROM table AS p
WHERE PICKS > 0
AND date = '2014-09-10'
GROUP BY name

But it keeps telling me that there is an incorrect use of the GROUP BY.

Upvotes: 1

Views: 35

Answers (2)

Senjai
Senjai

Reputation: 1816

You need to use a having statement. Not a where.

SELECT name, SUM(p.column1+p.column2+p.colum3) AS PICKS
FROM table AS p
AND date = '2014-09-10'
GROUP BY name
HAVING PICKS > 0

See http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Use having clause to filter out the results of aggregate functions

SELECT name, SUM(p.column1+p.column2+p.colum3) AS PICKS
FROM table AS p
WHERE date = '2014-09-10'
GROUP BY name
HAVING PICKS > 0

or you another way you can use sub select 

SELECT * FROM
(SELECT name, SUM(p.column1+p.column2+p.colum3) AS PICKS
FROM table AS p
WHERE date = '2014-09-10'
GROUP BY name) t
WHERE PICKS > 0

Upvotes: 1

Related Questions