MySQL query - get column values "greater than..."

Question

I have this query:

$score = 10;
SELECT timecode, count(tag) as n_tags, tag
FROM dados
WHERE dados.tag = 'tag1' 
AND dados.filename = 'file.mp4'
AND (timecode >= '-5' AND timecode <= '15')
AND (timecode = '$score')
GROUP BY timecode
ORDER BY count(tag) DESC

However I want to change the 6 line to:

AND (timecode = '$score' AND n_tags > 3)

but it doesn't seems the correct way to do, it doesn't work.

Any ideas ?

You are both checking equality timecode = '$score' and checking if it is in a range timecode >= -5 AND timecode <= 15. Is $score inside that range? (so why do you need the range at all?) — Michael Berkowski
– Michael Berkowski, Commented Feb 20, 2013 at 14:32
Test that $score is between -5 and 15 before making your db query, and only execute the query if it is - then you can eliminate AND (timecode >= '-5' AND timecode <= '15') — Mark Baker
– Mark Baker, Commented Feb 20, 2013 at 14:33

Tom · Accepted Answer · 2013-02-20 14:32:23Z

2

Remove that line from your WHERE clause and put in a HAVING clause. A HAVING clause will look at aggregate results. WHERE does not.

SELECT timecode, count(tag) as n_tags, tag
FROM dados
WHERE dados.tag = 'tag1' 
AND dados.filename = 'file.mp4'
AND (timecode >= '-5' AND timecode <= '15')
AND (timecode = '$score')
GROUP BY timecode
HAVING count(tag) > 3
ORDER BY count(tag) DESC

This line is redundant and could be dropped: AND (timecode >= '-5' AND timecode <= '15')

SELECT timecode, count(tag) as n_tags, tag
FROM dados
WHERE dados.tag = 'tag1' 
AND dados.filename = 'file.mp4'
AND (timecode = '$score')
GROUP BY timecode
HAVING count(tag) > 3
ORDER BY count(tag) DESC

answered Feb 20, 2013 at 14:32

Tom

6,6633 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tom Over a year ago

A HAVING clause will look at aggregate results. WHERE does not.

Michael Berkowski Over a year ago

@Tom The point is that your answer needs to include that information. A solution without an explanation isn't really an answer - it's just, well, a solution.

John Woo Over a year ago

n_tags is an alias of an aggregated column. do you think alias define on the same level can be used on having clause?

Tom Over a year ago

Yes, the alias can be used in the HAVING clause. I generally don't do it that way but it can.

John Woo Over a year ago

@Tom Correct. I just tested it. (I don't know why this works on mysql) In other RDBMS you can't.

|

Marc B · Accepted Answer · 2013-02-20 14:33:26Z

2

You can't use aggregated fields in a WHERE clause. count() results are only available after ALL applicable rows have been considered. But the where filtering is done as each individual row is parsed. Cliche statement, but you're trying to count your chickens before they've hatched.

Such things have to be done with a HAVING clause, which is basically exactly the same as a where, but is applied just before the final results are sent back to the client. So...

SELECT ..., count(tag) as n_tags
...
WHERE (timecode = '$score')

HAVING (n_tags > 3)

answered Feb 20, 2013 at 14:33

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

MySQL query - get column values "greater than..."

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related