Nested Group by SQL - Help Needed

karthick Ramesh · New User Joined: 19 Mar 2008 Posts: 12 Location: India

Hi All,
I have 3 columns in my table

Code Mktcode Id
1 10 0001
2 20 0010
3 30 0012
4 40 0013
1 20 0014
.....

so there can be many Ids with the following Code(1,2,3,4) and Mktcode(10,20,30,40).

Currently, i have performed to make a count on Each status code Group by status code by using the below SQL,

SELECT STATUS_CD,COUNT(STATUS_CD)
FROM Table GROUP BY STATUS_CD
Result:
1 2
2 1
3 1
4 1

But Unfortunately i have make the count on status code based on the market code

Expecting result:
010
1 1
2 0
3 0
4 0
020
1 1
2 1
3 0
4 0
030
1 0
2 0
3 1
4 0
040
1 0
2 0
3 0
4 1

Can someone please help me on this to get my desired value!!!

Suresh Ponnusamy · Posted: Thu Aug 07, 2008 7:11 pm

Your Expecting result is confusing.. What is first column and second column representing here...

If you want to make the count on status code based on the market code
then the result should be as follows rite.. please confirm.

Market Code Status Code Count
10 - 1
20 - 2
30 - 1
40 - 1

mkarthikeyan · New User Joined: 07 Aug 2008 Posts: 34 Location: Bangalore

Group the two columns MKTCODE & STATUS_CD

SELECT MKTCODE,STATUS_CD,COUNT(STATUS_CD) AS CNT
FROM Table GROUP BY MKTCODE,STATUS_CD

The result would be

MKTCODE--STATUS--CNT
10--1--1
20--1--1
20--2--1
30--3--1
40--4--1

Thanks
karthikeyan m

[S/360] was the biggest, riskiest decision I ever made, and I agonized about it for weeks,but deep down I believed there was nothing IBM couldn't do.

- Tom Watson, Jr. IBM Chairman and CEO 1961-1971