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
 

 

grouping and counting based on a column of table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Sat Jun 26, 2010 10:03 am    Post subject: grouping and counting based on a column of table
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Jun 28, 2010 1:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Tue Jun 29, 2010 12:46 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Jun 29, 2010 1:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Sat Jul 03, 2010 8:34 am    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Jul 05, 2010 1:57 pm    Post subject:
Reply with quote

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
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 Data replication from multiple Db2 ta... kishpra DB2 1 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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