March 18, 2009 at 8:24 pm
Let us assume the following condition, here there is table with following data.
Now if we want to know A's, B's, C's, D's are there we can use the following query
SELECT ItemNumber, SUM(NumCount) FROM Test WHERE Catagory = 'A'
So the result will be as follow
1 | 3
2 | 1
Similarly for the given query
SELECT ItemNumber, SUM(NumCount) FROM Test WHERE Catagory = 'B'
So the result will be as follow
1 | 3
3 | 2
Now my problem is the above query doesn't display ItemNumbers having result 0
For e.g.
For first query result should have been
1 | 3
2 | 1
3 | 0
And for second query the result should have been
1 | 3
2 | 0
3 | 2
I have tried many ways but this problem still eludes me.
Can some post a correct query for me.
Thanks in advance.
CREATE TABLE Test(
ItemNumber char(12),
NumCount Int,
Catagory VARCHAR(20)
)
INSERT INTO Test VALUES ('1', 2, 'A')
INSERT INTO Test VALUES ('1', 1, 'A')
INSERT INTO Test VALUES ('1', 3, 'B')
INSERT INTO Test VALUES ('1', 1, 'C')
INSERT INTO Test VALUES ('2', 1, 'A')
INSERT INTO Test VALUES ('2', 3, 'C')
INSERT INTO Test VALUES ('2', 1, 'C')
INSERT INTO Test VALUES ('3', 2, 'D')
INSERT INTO Test VALUES ('3', 2, 'D')
INSERT INTO Test VALUES ('3', 2, 'B')
March 18, 2009 at 8:29 pm
Check out a blog that I have on my site for doing this. You can use a cartesian product to create a list and plug zeros for values that have no aggregate. I think if you search on cross joins you'll find it.
March 18, 2009 at 8:44 pm
I looked at various cross join methods but they require two tables but in my case I have only one so, are there any easy solutions for one table.
I can try creating two tables if I have no solution for my problem.
Thanks for the info on cross join.
March 18, 2009 at 11:11 pm
Hi,
Try this, It will give you the result you are looking for.
CREATE TABLE #Test(
ItemNumber char(12),
NumCount Int,
Catagory VARCHAR(20)
)
INSERT INTO #Test VALUES ('1', 2, 'A')
INSERT INTO #Test VALUES ('1', 1, 'A')
INSERT INTO #Test VALUES ('1', 3, 'B')
INSERT INTO #Test VALUES ('1', 1, 'C')
INSERT INTO #Test VALUES ('2', 1, 'A')
INSERT INTO #Test VALUES ('2', 3, 'C')
INSERT INTO #Test VALUES ('2', 1, 'C')
INSERT INTO #Test VALUES ('3', 2, 'D')
INSERT INTO #Test VALUES ('3', 2, 'D')
INSERT INTO #Test VALUES ('3', 2, 'B')
SELECT DISTINCT #TEST.ITEMNUMBER, ISNULL(CT,0) FROM
(
SELECT
ITEMNUMBER
,SUM(ISNULL(NUMCOUNT,0)) CT
FROM
#TEST
WHERE
CATAGORY = 'A'
GROUP BY
ITEMNUMBER
)TBL
RIGHT OUTER JOIN
#TEST
ON #TEST.ITEMNUMBER = TBL.ITEMNUMBER
SELECT DISTINCT #TEST.ITEMNUMBER, ISNULL(CT,0) FROM
(
SELECT
ITEMNUMBER
,SUM(ISNULL(NUMCOUNT,0)) CT
FROM
#TEST
WHERE
CATAGORY = 'B'
GROUP BY
ITEMNUMBER
)TBL
RIGHT OUTER JOIN
#TEST
ON #TEST.ITEMNUMBER = TBL.ITEMNUMBER
DROP TABLE #Test
March 19, 2009 at 1:07 am
Thank you very very much. You guys save my day. I am very grateful. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply