Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

[Solved]Problem with Group by

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Problem with Group by
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: 1452
Location: Chicago, IL

PostPosted: Mon Dec 05, 2005 7:19 pm    Post subject: Re: Problem with Group by
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    Post subject: Re: Problem with Group by
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: 1452
Location: Chicago, IL

PostPosted: Tue Dec 06, 2005 4:06 pm    Post subject: Re: Problem with Group by
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    Post subject: Re: Problem with Group by
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us