|
View previous topic :: View next topic
|
| Author |
Message |
sagarnsydney
New User
Joined: 12 Dec 2008
Posts: 16
Location: sydney
|
|
|
|
Hi All,
I have a below requirement. Could you please help me in query.
I have two columns in a table.
AA 1
AA 2
AA 3
AA 4
AA 5
BB 1
BB 2
BB 3
BB 4
BB 5
I need output like below
AA LESS3 2
AA MORE3 3
BB LESS3 2
BB MORE3 3
I have to group first column and I have to count second column which are less than 3 and more than or equal to 3.
Please let me know whether this can be done in DB2.
Thank you in advance.
Sagar |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select col1
, case when col2 < 3 then 'LESS3' else 'MORE3' end
, count(*)
from tab1
group by col1
, case when col2 < 3 then 'LESS3' else 'MORE3' end |
|
|
| Back to top |
|
|
sagarnsydney
New User
Joined: 12 Dec 2008
Posts: 16
Location: sydney
|
|
|
|
Hi GuyC,
Thanks for the information. But, I am getting SQLSTATE=56038 when I ran the query. I further explored on this and it is mentioned that this error is generated based on QMF set up.
Please let me know if there is any other way to achieve this.
Once again thanks for the info.
Regards,
Sagar |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
56038 is -4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE ?
What's the version of your DB2 ?
"Group by expression" is V8 NFM
| Code: |
select a.col1, a.mycol, count(*)
from
(select col1
, case when col2 < 3 then 'LESS3' else 'MORE3' end as mycol
from tab1 ) A
group by a.col1, a.mycol |
|
|
| Back to top |
|
|
sagarnsydney
New User
Joined: 12 Dec 2008
Posts: 16
Location: sydney
|
|
|
|
Hi GuyC,
Thank you and it is working now.
But, I need to verify one more condition and it is some thing as below:
AA 1 A
AA 2 B
AA 3 C
AA 4 D
AA 5 E
BB 1 A
BB 2 B
BB 3 C
BB 4 D
BB 5 E
I need to validate col3 values against one new table TAB3 and it has values A,C and D.
Means the count is 1 if col3 value is present in new TAB3.
Hence, the output shows as below:
AA LESS3 2 YES 1
AA LESS3 2 NO 1
AA MORE3 3 YES 2
AA MORE3 3 NO 1
BB LESS3 2 YES 1
BB LESS3 2 NO 1
BB MORE3 3 YES 2
BB MORE3 3 NO 1
Can I put one more condition to validate against new table.
Thank you in advance.
Regards,
Sagar |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
1) you've given way too little information.
"against Tab3" How do you join tab1 with tab3 ?
How many corresponding rows in tab3 ? always 1 ? sometimes 0 ? sometimes more ?
2) you're combining a sum/count on one level with a sum/count on another level. this wil make your SQL rather complicated.
Actually you're trying to do too much in one statement.
But it will be something like below = previous solution applied 2 times.
| Code: |
select X2.col1,x2.mycol1, x2.cnt1, Y2.mycol2, Y2.cnt2 from
(select x1.col1, x1.mycol, count(*) as cnt1 from
(select col1
, case when col2 < 3 then 'LESS3' else 'MORE3' end as mycol1
from tab1 ) x1
group by a.col1, a.mycol1
) X2
,
(select col1 ,mycol1, mycol2, count(*) as cnt2 from
(select col1
, case when col2 < 3 then 'LESS3' else 'MORE3' end as mycol1
, case when col3 in ('A','B','C') then 'YES' else 'NO' end as mycol2
from tab1 , tab3
where tab1.col2 = tab3.col2) Y1
group by y1.col1, y1.mycol1, y1.mycol2
) Y2
where x2.col1 = X2.col1 and x2.mycol1 = y2.mycol1
|
|
|
| Back to top |
|
|
|
|
