ORDER BY AND GROUP BY
Posted on 2007-10-09
hi guys ,
I have this following query which i am trying to optimize:
SELECT A.ID AS id,
WHEN T.BILLID = 'ABC' THEN T.BILLAMT
END) AS nightbill,
WHEN T.BILLID = 'Off' THEN T.BILLAMT
END) AS offbill,
WHEN T.BILLID = 'Peak' THEN T.BILLAMT
END) AS peakbill
FROM STOCKPLANNIG A
INNER JOIN STOCKVERSIONS B
ON B.ID = A.ID
INNER JOIN STOCKAMT T
ON T.ID = A.ID
WHERE A.IND = 'r'
AND A.ID = 1
GROUP BY A.ID
ORDER BY A.ID
OPTIMIZE FOR 1 ROW
FETCH FIRST 201 ROWS ONLY
Above is my query in which i am passing the Id and retrieving nightbill, offbill and peakbill
My question is i have the GROUP BY and ORDER BY clause in my sql.
I need to use ORDER BY clause because i am displaying the records in ascending order.
I am not sure what is the use of GROUP BY clause.
If i dont put GROUP BY clause, i get this error:
A SELECT statement with no GROUP BY clause contains a column name and a column function in the SELECT clause, or a column name is contained in the SELECT clause but not in the GROUP BY clause. SQLSTATE=42803.
I have two questions
1.Can any one tell me why i need the GROUP BY clause and if i remove it why am i getting the error?
2.Do i really need to use GROUP BY clause and ORDER BY clause or can i just use GROUP BY clause.
I cannot avoid the GROUP BY clause because i get the error. Can i avoid the ORDER BY clause in this scenario.
I would greatly appreciate any help or sugessions from anyone.
Thanks very much