SQL DB2 query error

Question

I'm trying to get a hang on SQL, but I dont know why this doesn't work.

    SELECT p.Ort, COUNT(Projekt.Ort) AS Anzahl
    FROM Projekt p
    WHERE Anzahl > 2 GROUP BY p.Ort;

If I try to use this I get:

    "ANZAHL" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.9.78

valex · Accepted Answer · 2014-05-23 09:01:12Z

1

You should HAVING instead of WHERE

  SELECT p.Projektort, COUNT(Projekt.Projektort) AS ProjektAnzahl
  FROM Projekt p
  GROUP BY p.Projektort
  HAVING COUNT(Projekt.Projektort) > 2 ;

answered May 23, 2014 at 9:01

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

1 Comment

WHERE is used to choose which individual rows to use from the table(s) before processong them. HAVING is evaluated after grouping.

Josh · Accepted Answer · 2014-05-23 09:00:50Z

1

In GROUP BY clauses, the HAVING keyword is used:

SELECT p.Projektort, COUNT(Projekt.Projektort) AS ProjektAnzahl
FROM Projekt p
GROUP BY p.Projekt
HAVING ProjektAnzahl > 2

answered May 23, 2014 at 9:00

Josh

8,0265 gold badges44 silver badges63 bronze badges