getting error in using the having and group by clause in oracle

hHis is my table dummy:

开发者_JAVA百科OUTPUT  OQTY    INPUT   IQTY    EN      FLEX
Y3      1       X       2       Y       0
Y4      1       X       2       Y       0
Y5      1       X       2       N       0
Y6      1       X       2       N       0
Y1      1       X       2       Y       0
Y2      1       X       2       Y       0

And my query is:

SELECT INPUT,count(*)
FROM dummy
GROUP BY INPUT
HAVING EN = 'Y';

I am getting an error not a group by statement.

I want a count of similar inputs with en = Y.

What am I doing wrong here?

---

You don't need to use having here, it's generally for checking conditions that can only be calculated after grouping is done (like having count(*) > 2 or similar).

Use something like:

select    input,
          count(*) as quantity
from      dummy
where     en = 'Y'
group by  input

The reason you can't use having en = 'Y' is because you've already loaded up all the rows and grouped them before the having clause kicks in. In other words, you no longer have the en column information, it's been lost in the aggregation.

---

See transcript below:

>  DROP TABLE DUMMY%
>  CREATE TABLE DUMMY (OUTPUT CHAR(2), OQTY INTEGER,
>>    INPUT CHAR(1), IQTY INTEGER, EN CHAR(2), FLEX INTEGER)%
>  INSERT INTO DUMMY (OUTPUT, OQTY, INPUT, IQTY, EN, FLEX) VALUES
>>    ('Y3',1,'X',2,'Y',0), ('Y4',1,'X',2,'Y',0), ('Y5',1,'X',2,'N',0), 
>>    ('Y6',1,'X',2,'N',0), ('Y1',1,'X',2,'Y',0), ('Y2',1,'X',2,'Y',0)%

>  SELECT * FROM DUMMY ORDER BY EN, INPUT, OUTPUT%
   OUTPUT  OQTY  INPUT  IQTY  EN  FLEX
   ------  ----  -----  ----  --  ----
   Y5         1  X         2  N      0
   Y6         1  X         2  N      0
   Y1         1  X         2  Y      0
   Y2         1  X         2  Y      0
   Y3         1  X         2  Y      0
   Y4         1  X         2  Y      0

>  SELECT      INPUT, COUNT(*) AS QUANT
>>    FROM     DUMMY
>>    WHERE    EN = 'Y'
>>    GROUP BY INPUT%
   INPUT  QUANT
   -----  -----
   X          4

And, updating one of the relevant rows:

>  UPDATE DUMMY SET INPUT = 'Z' WHERE OUTPUT = 'Y1'%   
>  SELECT * FROM DUMMY ORDER BY EN, INPUT, OUTPUT%
   OUTPUT  OQTY  INPUT  IQTY  EN  FLEX
   ------  ----  -----  ----  --  ----
   Y5         1  X         2  N      0
   Y6         1  X         2  N      0
   Y2         1  X         2  Y      0
   Y3         1  X         2  Y      0
   Y4         1  X         2  Y      0
   Y1         1  Z         2  Y      0

>  SELECT      INPUT, COUNT(*) AS QUANT
>>    FROM     DUMMY
>>    WHERE    EN = 'Y'
>>    GROUP BY INPUT%
   INPUT  QUANT
   -----  -----
   X          3
   Z          1

---

Instead of Count(*), define the column name which get to be counted.

check below

SELECT INPUT,count(EN) As TotalEnCount
FROM [dummy]
where EN = 'Y'
GROUP BY INPUT