?
Solved

ORDER BY AND GROUP BY

Posted on 2007-10-09
6
Medium Priority
?
3,406 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:jaggernat
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
ocgstyles earned 2000 total points
ID: 20045524
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
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20045533
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
 
LVL 5

Assisted Solution

by:ocgstyles
ocgstyles earned 2000 total points
ID: 20045568
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Author Comment

by:jaggernat
ID: 20045684
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
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20045890
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
 
LVL 10

Author Comment

by:jaggernat
ID: 20051318
thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question