January 16, 2009 at 12:01 am
Hi,
There are two tables here "Project table" and "Category table"
I need a output as shown in the output table.
Output:
1. In the output there must be each and every item from Category table.
2. In total column wherever the category doesn't appear, it must be displayed as 0.
I am confused how to get that output.
Please help me out in solving this.
Project table
PROJECT_IDCATEGORYTOTAL
1config3
1functionality2
1cosmetic 1
2config2
2func1
2cosmetic 3
2validation 2
2security1
2design4
3config2
Category table
CATEGORY
config
func
cosmetic
validation
security
design
Output table must look like
PROJECT_IDCATEGORYTOTAL
1config3
1func2
1cosmetic 1
1validation 0
1security0
1design0
2config2
2func1
2cosmetic 3
2validation 2
2security1
2design4
3config2
3func0
3cosmetic 0
3validation 0
3security0
3design0
Thanks,
Santhosh
January 16, 2009 at 1:28 am
here's your answer...
DROP TABLE tmpPoj
DROP TABLE tmpCat
CREATE TABLE tmpPoj
( PojID INT, Cat VARCHAR(10), Total INT )
CREATE TABLE tmpCat
( CatIDINT, Cat VARCHAR(10) )
INSERTtmpPoj
SELECT1, 'A', 1 UNION
SELECT1, 'B', 2 UNION
SELECT2, 'C', 10 UNION
SELECT3, 'D', 8
INSERTtmpCat
SELECT1, 'A' UNION
SELECT2, 'B' UNION
SELECT3, 'C' UNION
SELECT4, 'D' UNION
SELECT5, 'E' UNION
SELECT6, 'F'
SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat, tmpPoj.Total as STo, tmpCat.CatID
FROMtmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Abhijit - http://abhijitmore.wordpress.com
January 16, 2009 at 2:08 am
Abhijit More (1/16/2009)
here's your answer...DROP TABLE tmpPoj
DROP TABLE tmpCat
CREATE TABLE tmpPoj
( PojID INT, Cat VARCHAR(10), Total INT )
CREATE TABLE tmpCat
( CatIDINT, Cat VARCHAR(10) )
INSERTtmpPoj
SELECT1, 'A', 1 UNION
SELECT1, 'B', 2 UNION
SELECT2, 'C', 10 UNION
SELECT3, 'D', 8
INSERTtmpCat
SELECT1, 'A' UNION
SELECT2, 'B' UNION
SELECT3, 'C' UNION
SELECT4, 'D' UNION
SELECT5, 'E' UNION
SELECT6, 'F'
SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat, tmpPoj.Total as STo, tmpCat.CatID
FROMtmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Hi Abhijith, thank you for the reply
This worked fine without the SUM function
SELECT tmp.PojID, tmp.Cat,
------------------SUM removed
(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat, tmpPoj.Total as STo, tmpCat.CatID
FROMtmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Thanks,
Santhosh
January 16, 2009 at 2:23 am
Santhosh (1/16/2009)
Abhijit More (1/16/2009)
Hi Abhijith, thank you for the replyThis worked fine without the SUM function
SELECT tmp.PojID, tmp.Cat,
------------------SUM removed
(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat, tmpPoj.Total as STo, tmpCat.CatID
FROMtmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Are you sure ??? - It seems now to me! :hehe:
January 16, 2009 at 2:46 am
Though the solution provided will give the correct results, but will not perform very well on large amount of rows. You should be careful while using CROSS JOIN as it gives the cartesian product (i.e. Table1 rows X Table2 rows) and sometimes it yields to triangular joins.
For more information on triangular joins, see this article http://www.sqlservercentral.com/links/276514/84234 by Jeff Moden..
Here is an updated version of the query and it should perform better...
SELECTPC.PROJECT_ID, PC.CATEGORY, ISNULL( Proj.TOTAL, 0 ) AS TOTAL
FROM(
SELECTP.PROJECT_ID, C.CATEGORY
FROM(
SELECTDISTINCT PROJECT_ID
FROMProject
) P
CROSS JOIN Category C
) PC
LEFT JOIN ON Proj.PROJECT_ID = P.PROJECT_ID AND Proj.CATEGORY = C.CATEGORY
--Ramesh
January 16, 2009 at 2:58 am
Ramesh (1/16/2009)
Though the solution provided will give the correct results, but will not perform very well on large amount of rows. You should be careful while using CROSS JOIN as it gives the cartesian product (i.e. Table1 rows X Table2 rows) and sometimes it yields to triangular joins.For more information on triangular joins, see this article http://www.sqlservercentral.com/links/276514/84234 by Jeff Moden..
Here is an updated version of the query and it should perform better...
SELECTPC.PROJECT_ID, PC.CATEGORY, ISNULL( Proj.TOTAL, 0 ) AS TOTAL
FROM(
SELECTP.PROJECT_ID, C.CATEGORY
FROM(
SELECTDISTINCT PROJECT_ID
FROMProject
) P
CROSS JOIN Category C
) PC
LEFT JOIN ON Proj.PROJECT_ID = P.PROJECT_ID AND Proj.CATEGORY = C.CATEGORY
Not working this query ...are you sure does it works!?
January 16, 2009 at 3:10 am
its wrking used the same query olny retrived distinct records from tmpPoj..:)
SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat
FROM (SELECT DISTINCT tmpPoj.PojID FROM tmpPoj) tmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Abhijit - http://abhijitmore.wordpress.com
January 16, 2009 at 3:38 am
Abhijit More (1/16/2009)
its wrking used the same query olny retrived distinct records from tmpPoj..:)SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat
FROM (SELECT DISTINCT tmpPoj.PojID FROM tmpPoj) tmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Hmm nice notice!
January 16, 2009 at 3:41 am
Abhijit More (1/16/2009)
its wrking used the same query olny retrived distinct records from tmpPoj..:)SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECTtmpPoj.PojID, tmpCat.Cat
FROM (SELECT DISTINCT tmpPoj.PojID FROM tmpPoj) tmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Thank you all,
I learnt somthing from here and you got me out of this problem...
Thanks,
Santhosh
January 16, 2009 at 4:08 am
Not working this query ...are you sure does it works!?
Actually, something has ate-up my code....:D:D, missed the table in LEFT JOIN....
its wrking used the same query olny retrived distinct records from tmpPoj.. [Smile]
SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECT tmpPoj.PojID, tmpCat.Cat
FROM (SELECT DISTINCT tmpPoj.PojID FROM tmpPoj) tmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Yes, it should be working..., but why does you are aggregating the columns when you have already have distinct records and doing a case when you already specified a left join???? Are you having too much coffee today???:D:D:D
Here is the corrected version....
SELECT PC.PROJECT_ID, PC.CATEGORY, ISNULL( Proj.TOTAL, 0 ) AS TOTAL
FROM (
SELECT P.PROJECT_ID, C.CATEGORY
FROM (
SELECT DISTINCT PROJECT_ID
FROM Project
) P
CROSS JOIN Category C
) PC
LEFT JOIN Project ON Proj.PROJECT_ID = P.PROJECT_ID AND Proj.CATEGORY = C.CATEGORY
--Ramesh
January 16, 2009 at 7:37 am
Ramesh (1/16/2009)
Not working this query ...are you sure does it works!?
Actually, something has ate-up my code....:D:D, missed the table in LEFT JOIN....
its wrking used the same query olny retrived distinct records from tmpPoj.. [Smile]
SELECT tmp.PojID, tmp.Cat,
SUM(Case when tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat THEN Total
Else 0 END) AS 'Total'
FROM (
SELECT tmpPoj.PojID, tmpCat.Cat
FROM (SELECT DISTINCT tmpPoj.PojID FROM tmpPoj) tmpPoj
CROSS JOIN tmpCat
)tmp
LEFT JOIN tmpPoj ON tmp.PojID = tmpPoj.PojID AND tmpPoj.Cat = tmp.Cat
GROUP BY tmp.PojID, tmp.Cat, tmpPoj.PojID , tmpPoj.Cat
Yes, it should be working..., but why does you are aggregating the columns when you have already have distinct records and doing a case when you already specified a left join???? Are you having too much coffee today???:D:D:D
Here is the corrected version....
SELECT PC.PROJECT_ID, PC.CATEGORY, ISNULL( Proj.TOTAL, 0 ) AS TOTAL
FROM (
SELECT P.PROJECT_ID, C.CATEGORY
FROM (
SELECT DISTINCT PROJECT_ID
FROM Project
) P
CROSS JOIN Category C
) PC
LEFT JOIN Project ON Proj.PROJECT_ID = P.PROJECT_ID AND Proj.CATEGORY = C.CATEGORY
Oh there are so many ways(effective) to put a query into action...
Thanks
Thanks,
Santhosh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply