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
LVL 10
jaggernatAsked:
Who is Participating?
 
ocgstylesConnect With a Mentor Commented:
Hi,

You need to use the GROUP BY clause when you use functions that aggregate the data.  Take an employee table for example:

select count(*) as count
from employee;

That would return the total number of employees in the table.  This statement uses the function COUNT() to count all the rows.

Now lets say you want to display the count of employees for each department.  In other words, you want to aggregate your data by department, you'll need to select the department number (or name) in your query too.  To get the count, you need to GROUP all department that are the same, together.  All depts 18 go together, all depts 24 go together, etc...  This is the purpose of the GROUP BY clause.

So given the above statement, to get counts by department, the statement would look like this:

select dept_no, count(*)
from employee
group by dept;

Your common aggregate functions are, COUNT, MIN, MAX, SUM, and AVG.  There are others, but these are the most popular.

More examples:

sum of salaries by dept:

select dept_no, sum(salary)
from employee
group by dept_no;

avg salary for each dept:

select dept_no, avg(salary)
from employee
group by dept_no;

Basically, any time you use an aggregate function and your select includes other columns that you are not using aggregate functions on, you need to include them in the GROUP BY clause.  

Here is more info on GROUP BY:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.sqlassist2.doc/sqlassist2/t_groups.htm

Here is more info on Aggregate Functions:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0000757.htm

Keith
0
 
ocgstylesCommented:
Also, the ORDER BY clause....

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
0
 
ocgstylesConnect With a Mentor Commented:
One more thing.  The group by clause should include columns, not column aliases.

This will NOT work:

select d.dept_name || ' (' || e.dept_no || ')' as dept, count(*) as count
from employee e inner join dept d
   on e.dept_no = d.dept_no
group by dept;

You need to do this:

select d.dept_name || ' (' || e.dept_no || ')' as dept, count(*) as count
from employee e inner join dept d
   on e.dept_no = d.dept_no
group by d.dept_name || ' (' || e.dept_no || ')';

Some people don't like doing this (for readability's sake), so they put this in a subquery and aggregate the data in an outer query, like this;

select x.dept, count(*) as count
from (
     select d.dept_name || ' (' || e.dept_no || ')' as dept
     from employee e inner join dept d
        on e.dept_no = d.dept_no
) as x
group by x.dept;

Keith
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
jaggernatAuthor Commented:
ok makes sense keith, thanks very much. can you also provide some recommendations on a similar question i have:   http://www.experts-exchange.com/Database/DB2/Q_22882755.html?cid=239#a20045597

thankyou
J
0
 
ocgstylesCommented:
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
0
 
jaggernatAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.