View previous topic :: View next topic
|
Author |
Message |
lrgopal
New User
Joined: 18 Oct 2005 Posts: 3
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
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 |
|
|
lrgopal
New User
Joined: 18 Oct 2005 Posts: 3
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
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 |
|
|
lrgopal
New User
Joined: 18 Oct 2005 Posts: 3
|
|
|
|
Thank you priyesh
It worked....
with regards
lrgopal |
|
Back to top |
|
|
|