MVSFORUMS.com :: View topic - collect ,grouping and counting columns from a table

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

collect ,grouping and counting columns from a table

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

enge
Beginner

Joined: 12 Oct 2004
Posts: 78
Topics: 39

Posted: Wed Nov 21, 2007 6:19 am Post subject: collect ,grouping and counting columns from a table

hi all,

Code:

A B
--------- ---------
220126 20180
20180 220126
220126 20180
20180 220126
20180 220126

I need a result like this:

Code:

220126 2 3

where "220126" is the subject of both two columns of the table while "2" is the counter of first column and 3 of the second one.

how can i do?
thanks in advance

jsharon1248
Intermediate

Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago

Posted: Wed Nov 21, 2007 9:31 am Post subject:

This will get you started in the right direction.

Code:

SELECT COL_NM,SUM(COL1_CNT),SUM(COL2_CNT)
FROM (
SELECT COLA AS COL_NM,COUNT(*) AS COL1_CNT,0 AS COL2_CNT
FROM SESSION.JS_TBL
GROUP BY COLA
UNION ALL
SELECT COLB AS COL_NM,0 COL1_CNT,COUNT(*) AS COL2_CNT
FROM SESSION.JS_TBL
GROUP BY COLB
) AS TEMP_TBL
GROUP BY COL_NM;

enge
Beginner

Joined: 12 Oct 2004
Posts: 78
Topics: 39

Posted: Wed Nov 21, 2007 9:40 am Post subject:

it really work! thanks a lot

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum