CODEWITHSUNDEEP
sqloracle-databaseoracle10gora-00904
anon
anon

Reputation:

SQl count() help with a select statement

I'm using this code:

  SELECT MACH_NO, 
         COUNT(MACH_NO) AS TOTAL_REPORTS
    FROM MAINTENANCE_LOG
GROUP BY MACH_NO;

...to retrieve some data which gives:

MACH_NO  TOTAL_REPORTS
----------------------
1        4
5        2
8        1
7        1

How can I retrieve only where total reports is bigger than three? I tried:

WHERE TOTAL_REPORTS > 3

...but it says

ORA-00904: "TOTAL_REPORTS": invalid identifier

Upvotes: 1

Views: 665

Answers (4)

Rich Adams
Rich Adams

Reputation: 26574

Since you've used a GROUP BY, you should use HAVING rather than WHERE. You also need to explicitly use COUNT(MACH_NO) rather than your alias of TOTAL_REPORTS.

So use HAVING COUNT(MACH_NO) > 3, rather than WHERE TOTAL_REPORTS > 3.

SELECT MACH_NO, 
       COUNT(MACH_NO) AS TOTAL_REPORTS 
FROM MAINTENANCE_LOG 
GROUP BY MACH_NO
HAVING COUNT(MACH_NO) > 3;

Upvotes: 3

René Nyffenegger
René Nyffenegger

Reputation: 40533

SELECT MACH_NO, COUNT(MACH_NO) AS TOTAL_REPORTS FROM MAINTENANCE_LOG GROUP BY MACH_NO having count(mach_no) > 3

Upvotes: 0

Salil
Salil

Reputation: 47512

Use HAVING CLAUSE 

SELECT MACH_NO, COUNT(MACH_NO) AS TOTAL_REPORTS FROM MAINTENANCE_LOG GROUP BY MACH_NO HAVING TOTAL_REPORTS > 3;

Upvotes: 1

Francisco Soto
Francisco Soto

Reputation: 10392

Instead of WHERE TOTAL_REPORTS > 3, try WHERE COUNT(MACH_NO) > 3.

Upvotes: -1

Related Questions