IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

[Solved]Problem with Group by


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
lrgopal

New User


Joined: 18 Oct 2005
Posts: 3

PostPosted: Mon Dec 05, 2005 6:02 pm
Reply with quote

Input file
********************************
Deptno lastname salary
********************************
a01 stern 4562
a02 irving 2569
a03 jim 2547
a04 gary 2578
d01 david 1234
d02 william 1234
d03 jenifer 1234
e01 stern 4562
e02 irving 2569
e03 jim 2547

select only dept starting with 'a' and 'd' and add the sal for depts as a group like starting with 'a' and 'd'.

Deptno salary
***************
a 12256
d 3702


i tried like this
select substr(deptno,1,1),sum(salary) from table
where deptname like 'a%' or deptname like 'd%'
group by substr(deptno,1,1) order by 1.

showing error in group by clause, also tried alias but still the same

OS: MVS XA
DB2 V4.0
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Mon Dec 05, 2005 7:19 pm
Reply with quote

I write this query...for what you looking for....

Code:
SELECT SUBSTR(DEPTNO,1,1), SUM(SALARY) FROM TABLE
WHERE DEPTNAME LIKE 'a%' OR DEPTNAME LIKE 'd%'
GROUP BY DEPTNO HAVING DEPTNAME LIKE 'a%' OR DEPTNAME LIKE 'd%'
ORDER BY 1;


Check & let me know the results...

Regards,

Priyesh.
Back to top
View user's profile Send private message
lrgopal

New User


Joined: 18 Oct 2005
Posts: 3

PostPosted: Tue Dec 06, 2005 1:46 pm
Reply with quote

hi

thnks for the reply, yes it is showing up but the grouping is not getting summed up.

for ex

A salary
A salary
D 1234
D 1234
D 1234


instead

it should come as
A sum of A dept salary
D sum of D dept salary

The A and D group is not getting summed up, plz check ..
What i meant was in the output the firstchar of the column with the respective sum of salaries starting with the first char of the col...
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Tue Dec 06, 2005 4:06 pm
Reply with quote

Irgopal,

Try this query...

Code:
SELECT TEMPCOL, SUM(SALARY)                                   
FROM (SELECT SALARY,SUBSTR(DEPTNO,1,1) AS TEMPCOL           
FROM TABLE) AS T                           
GROUP BY TEMPCOL HAVING TEMPCOL LIKE 'A%' OR TEMPCOL LIKE 'D%';


In DB2, you can not use GROUP BY clause with functions. Only trick is to define a sub-query like this.

Well, try & let me know your results.

Regards,

Priyesh.
Back to top
View user's profile Send private message
lrgopal

New User


Joined: 18 Oct 2005
Posts: 3

PostPosted: Wed Dec 07, 2005 9:36 am
Reply with quote

Thank you priyesh

It worked....


with regards
lrgopal
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts Compare latest 2 rows of a table usin... DB2 1
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts Job scheduling problem. JCL & VSAM 9
No new posts Problem with IFTHEN=(WHEN=GROUP,BEGIN... DFSORT/ICETOOL 5
Search our Forums:

Back to Top