SQl count() help with a select statement

Question

HI im 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

now 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

Answer 1

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

Answer 2

Use HAVING CLAUSE

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

Answer 3

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

Answer 4

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;