1

Here's my MySQL query and what it returns:

SELECT email, COUNT(*) AS num  
FROM collectors_users
WHERE subscribed != 'NO'
AND lastLogin IS NULL    
GROUP BY email
ORDER BY dateadded DESC;

enter image description here

I only want to return results where num > 1. I tried to change my query like this but it doesn't work saying that num is not a recognized column:

SELECT email, COUNT(*) AS num  
FROM collectors_users
WHERE subscribed != 'NO'
AND lastLogin IS NULL
AND num > 1
GROUP BY email
ORDER BY dateadded DESC;

How can I return results where num > 1?

Ethan Allen
  • 14,425
  • 24
  • 101
  • 194

5 Answers5

3

After the GROUP BY clause, and before the ORDER BY clause add this:

HAVING COUNT(*) > 1

The HAVING clause gets applied nearly last in the execution plan, after all of the rows have been prepared, prior to the LIMIT clause. This is most useful for conditions that can't be checked while rows are being accessed, but only after the rows have been accessed, such as an aggregate function like a COUNT(*), though it can be used for non-aggregates.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks for explaining why I should use `HAVING` instead of just posting a modified query. Appreciate it. – Ethan Allen Apr 02 '14 at 16:27
  • 1
    @EthanAllen: I think it's easier for you if I just highlight the change you need to make, as opposed to posting your whole query with one line added, with a comment that says "Try (finding what I changed in) this:" – spencer7593 Apr 02 '14 at 16:31
0

Use HAVING clause. Or another way:

SELECT * FROM
 (SELECT email, COUNT(*) AS num  
  FROM collectors_users
  WHERE subscribed != 'NO'
  AND lastLogin IS NULL    
  GROUP BY email
  ORDER BY dateadded DESC;
  ) T
WHERE num>1
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
0

I think you should use HAVING

SELECT email, COUNT(*) AS num  
FROM collectors_users
WHERE subscribed != 'NO'
AND lastLogin IS NULL
GROUP BY email
ORDER BY dateadded DESC
HAVING COUNT(*) > 1;

this question can help you to understand why HAVING and not WHERE

WHERE vs HAVING

Community
  • 1
  • 1
Farnabaz
  • 4,030
  • 1
  • 22
  • 42
0
SELECT email, COUNT(*) as num  
FROM collectors_users
WHERE subscribed != 'NO'
AND lastLogin IS NULL
GROUP BY email
HAVING COUNT(*) > 1
Farnabaz
  • 4,030
  • 1
  • 22
  • 42
Youssef DAOUI
  • 329
  • 1
  • 6
0

You can use Having clause.

SELECT email, COUNT(*) AS num  
FROM collectors_users
WHERE subscribed != 'NO'
AND lastLogin IS NULL
GROUP BY email
HAVING count(*) > 1
ORDER BY dateadded DESC;

for more information: http://www.w3schools.com/sql/sql_having.asp

Amytis
  • 91
  • 4