jaggernat
asked on
ORDER BY AND GROUP BY
hi guys ,
I have this following query which i am trying to optimize:
SELECT A.ID AS id,
sum(case
WHEN T.BILLID = 'ABC' THEN T.BILLAMT
END) AS nightbill,
sum(case
WHEN T.BILLID = 'Off' THEN T.BILLAMT
END) AS offbill,
sum(CASE
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
WITH UR
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
J
I have this following query which i am trying to optimize:
SELECT A.ID AS id,
sum(case
WHEN T.BILLID = 'ABC' THEN T.BILLAMT
END) AS nightbill,
sum(case
WHEN T.BILLID = 'Off' THEN T.BILLAMT
END) AS offbill,
sum(CASE
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
WITH UR
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
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok makes sense keith, thanks very much. can you also provide some recommendations on a similar question i have: https://www.experts-exchange.com/questions/22882755/Strange-Group-By-error.html?cid=239&anchorAnswerId=20045597#a20045597
thankyou
J
thankyou
J
Error:
select d.dept_name || ' (' || e.dept_no || ')' as dept
should be:
select d.dept_name || ' (' || char(e.dept_no) || ')' as dept
I meant for the department number to be an integer, so we need to cast to a character data type.
K
select d.dept_name || ' (' || e.dept_no || ')' as dept
should be:
select d.dept_name || ' (' || char(e.dept_no) || ')' as dept
I meant for the department number to be an integer, so we need to cast to a character data type.
K
ASKER
thanks
You only need to use that when you want the results sorted in a particular order. You don't NEED to use an ORDER BY clause when you use a GROUP BY clause.
Keith