CODEWITHSUNDEEP
mysqlsql
Jirka Kopřiva
Jirka Kopřiva

Reputation: 3089

MySQL - Where is right place for condition?

How to prevent sum(f)=0 from insert?

INSERT INTO `t_1` (a,b,c)
 SELECT d,e,sum(f)
 GROUP by d,e

I tried to add condition WHERE sum(f)>0 to the query end. But it ends with error.

Upvotes: 2

Views: 72

Answers (4)

Randy
Randy

Reputation: 16677

SELECT d,e,sum(f)  
FROM mytable
GROUP by d,e
HAVING sum(f) > 0

OR...

place some logic in a trigger that throws an exception if that value is 0.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

Try this:

INSERT INTO `t_1` (a,b,c)
SELECT   d,e,sum(f)
--FROM        -- don't forget this line  
GROUP by d,e
HAVING   sum(f) > 0

The difference between WHERE and HAVING clause is that the HAVING clause evaluates the aggregated (functions like SUM(), COUNT(), MAX(), etc...) condition while the WHERE clause cannot contain aggregate function.

Upvotes: 4

Ask Bjørn Hansen
Ask Bjørn Hansen

Reputation: 6943

Before GROUP BY, you can see the select syntax in the MySQL documentation, it should be similar or the same when you have 'insert into' in front.

(Though maybe you meant 'HAVING ...' rather than 'WHERE').

Upvotes: 1

gview
gview

Reputation: 15361

When using a GROUP by you can filter out groups using the HAVING clause.

SELECT d,e,sum(f)  
GROUP by d,e
HAVING sum(f) > 0

Upvotes: 4

Related Questions