Summarize the content in multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summarize the content in multiple columns

Author

Topic

goduncan
Starting Member

1 Post

Posted - 2010-04-23 : 13:41:44

I have the following table (TEST) with 5 columns (PID, Comp1, Comp2, Comp3 & Comp4) for which I need the unique occurrence count for each column.

PID Comp1 Comp2 Comp3 Comp4
1 EQUAL DIFF EQUAL LOSS
2 EQUAL NULL GAIN EQUAL
3 LOSS GAIN NULL EQUAL
4 GAIN EQUAL EQUAL EQUAL
5 DIFF DIFF NULL GAIN

Desired result:

Result Comp1 Count Comp2 Count Comp3 Count Comp4 Count
GAIN 1 1 1 1
LOSS 1 0 0 1
DIFF 1 2 0 0
NULL 0 1 2 0
EQUAL 2 1 2 3

I've tried subqueries with DISTINCT and can't seem to get the right combination to achieve the desired result. Any help would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:01:12

[code]
SELECT Val,
SUM(CASE WHEN Comp='Comp1' THEN 1 ELSE 0 END) AS Comp1,
SUM(CASE WHEN Comp='Comp2' THEN 1 ELSE 0 END) AS Comp2,
SUM(CASE WHEN Comp='Comp3' THEN 1 ELSE 0 END) AS Comp3,
SUM(CASE WHEN Comp='Comp4' THEN 1 ELSE 0 END) AS Comp4
FROM Table
UNPIVOT (Val FOR Comp IN ([Comp1],[Comp2],[Comp3],[Comp4]))u
GROUP BY Val
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 14:21:41

I think this solution is correct answer.

SELECT   D.Comp AS Result,
         (SELECT COUNT(1) FROM tbl WHERE Comp1 = D.Comp) AS Comp1_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp2 = D.Comp) AS Comp2_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp3 = D.Comp) AS Comp3_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp4 = D.Comp) AS Comp4_Count
FROM
(
SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl
) D(Comp)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:27:42

quote:

Originally posted by ms65g

I think this solution is correct answer.

SELECT   D.Comp AS Result,
         (SELECT COUNT(1) FROM tbl WHERE Comp1 = D.Comp) AS Comp1_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp2 = D.Comp) AS Comp2_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp3 = D.Comp) AS Comp3_Count,
         (SELECT COUNT(1) FROM tbl WHERE Comp4 = D.Comp) AS Comp4_Count
FROM
(
SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl
) D(Comp)

Ok I get what you're suggesting. I had missed NULL part but even then the above suggestion is not giving expected output



Result	Comp1_Count	Comp2_Count	Comp3_Count	Comp4_Count
NULL	0	0	0	0
DIFF	1	2	0	0
EQUAL	2	1	2	3
GAIN	1	1	1	1
LOSS	1	0	0	1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:29:40

ok.i've accounted for NULL also now

use below:-


SELECT NULLIF(Val,'NULL') AS Val,
SUM(CASE WHEN Comp='Comp1' THEN 1 ELSE 0 END) AS Comp1,
SUM(CASE WHEN Comp='Comp2' THEN 1 ELSE 0 END) AS Comp2,
SUM(CASE WHEN Comp='Comp3' THEN 1 ELSE 0 END) AS Comp3,
SUM(CASE WHEN Comp='Comp4' THEN 1 ELSE 0 END) AS Comp4
FROM (SELECT PID,
        ISNULL(Comp1,'NULL') AS Comp1,
        ISNULL(Comp2,'NULL') AS Comp2,
        ISNULL(Comp3,'NULL') AS Comp3,
        ISNULL(Comp4,'NULL') AS Comp4
      FROM  YourTable) t
UNPIVOT (Val FOR Comp IN ([Comp1],[Comp2],[Comp3],[Comp4]))u
GROUP BY Val


output
-------------------------------------------------------
Val	Comp1	Comp2	Comp3	Comp4
DIFF	1	2	0	0
EQUAL	2	1	2	3
GAIN	1	1	1	1
LOSS	1	0	0	1
NULL	0	1	2	0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 14:36:17

Test it please.

SELECT   D.Comp AS Result,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp1, '1') = COALESCE(D.Comp ,'1')) AS Comp1_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp2, '1') = COALESCE(D.Comp ,'1')) AS Comp2_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp3, '1') = COALESCE(D.Comp ,'1')) AS Comp3_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp4, '1') = COALESCE(D.Comp ,'1')) AS Comp4_Count
FROM
(
SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl
) D(Comp)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:38:37

quote:

Originally posted by ms65g

Test it please.

SELECT   D.Comp AS Result,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp1, '1') = COALESCE(D.Comp ,'1')) AS Comp1_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp2, '1') = COALESCE(D.Comp ,'1')) AS Comp2_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp3, '1') = COALESCE(D.Comp ,'1')) AS Comp3_Count,
         (SELECT COUNT(1) FROM tbl WHERE COALESCE(Comp4, '1') = COALESCE(D.Comp ,'1')) AS Comp4_Count
FROM
(
SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl
) D(Comp)

this modification will work.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-25 : 16:04:52

I like madhivanan's advice, so I post a method that tomorrow he wills advice me.



;WITH itmes AS 
(SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl), c1 AS
(SELECT COUNT(*) AS comp1, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp1
 GROUP BY t1.comp), c2 as
 (SELECT COUNT(*) AS comp2, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp2
 GROUP BY t1.comp), c3 as
 (SELECT COUNT(*) AS comp3, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp3
 GROUP BY t1.comp), c4 as
 (SELECT COUNT(*) AS comp4, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp4
 GROUP BY t1.comp)
 
SELECT i.comp
, ISNULL(c1.comp1, 0) AS Comp1
, ISNULL(c2.comp2, 0) AS Comp2
, ISNULL(c3.comp3, 0) AS Comp3
, ISNULL(c4.comp4, 0) AS Comp4
FROM items i
LEFT OUTER JOIN c1 on i.comp=c1.comp
LEFT OUTER JOIN c2 on i.comp=c2.comp
LEFT OUTER JOIN c3 on i.comp=c3.comp
LEFT OUTER JOIN c4 on i.comp=c4.comp
EXCEPT 
SELECT NULL,0,0,0,0;

--======================================
--========    Equivalence    ===========
--======================================
SELECT Val,
SUM(CASE WHEN Comp='Comp1' THEN 1 ELSE 0 END) AS Comp1,
SUM(CASE WHEN Comp='Comp2' THEN 1 ELSE 0 END) AS Comp2,
SUM(CASE WHEN Comp='Comp3' THEN 1 ELSE 0 END) AS Comp3,
SUM(CASE WHEN Comp='Comp4' THEN 1 ELSE 0 END) AS Comp4
FROM tbl
UNPIVOT (Val FOR Comp IN ([Comp1],[Comp2],[Comp3],[Comp4]))u
GROUP BY Val

/*
comp       Comp1       Comp2       Comp3       Comp4
---------- ----------- ----------- ----------- -----------
DIFF       1           2           0           0
EQUAL      2           1           2           3
GAIN       1           1           1           1
LOSS       1           0           0           1

(4 row(s) affected)

Val        Comp1       Comp2       Comp3       Comp4
---------- ----------- ----------- ----------- -----------
DIFF       1           2           0           0
EQUAL      2           1           2           3
GAIN       1           1           1           1
LOSS       1           0           0           1

(4 row(s) affected)
*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 00:37:06

quote:

Originally posted by ms65g

I like madhivanan's advice, so I post a method that tomorrow he wills advice me.



;WITH itmes AS 
(SELECT Comp1 FROM tbl
UNION
SELECT Comp2 FROM tbl
UNION
SELECT Comp3 FROM tbl
UNION
SELECT Comp4 FROM tbl), c1 AS
(SELECT COUNT(*) AS comp1, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp1
 GROUP BY t1.comp), c2 as
 (SELECT COUNT(*) AS comp2, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp2
 GROUP BY t1.comp), c3 as
 (SELECT COUNT(*) AS comp3, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp3
 GROUP BY t1.comp), c4 as
 (SELECT COUNT(*) AS comp4, t1.comp
 FROM items t1
 INNER JOIN tbl t2
 ON t1.comp = t2.comp4
 GROUP BY t1.comp)
 
SELECT i.comp
, ISNULL(c1.comp1, 0) AS Comp1
, ISNULL(c2.comp2, 0) AS Comp2
, ISNULL(c3.comp3, 0) AS Comp3
, ISNULL(c4.comp4, 0) AS Comp4
FROM items i
LEFT OUTER JOIN c1 on i.comp=c1.comp
LEFT OUTER JOIN c2 on i.comp=c2.comp
LEFT OUTER JOIN c3 on i.comp=c3.comp
LEFT OUTER JOIN c4 on i.comp=c4.comp
EXCEPT 
SELECT NULL,0,0,0,0;

--======================================
--========    Equivalence    ===========
--======================================
SELECT Val,
SUM(CASE WHEN Comp='Comp1' THEN 1 ELSE 0 END) AS Comp1,
SUM(CASE WHEN Comp='Comp2' THEN 1 ELSE 0 END) AS Comp2,
SUM(CASE WHEN Comp='Comp3' THEN 1 ELSE 0 END) AS Comp3,
SUM(CASE WHEN Comp='Comp4' THEN 1 ELSE 0 END) AS Comp4
FROM tbl
UNPIVOT (Val FOR Comp IN ([Comp1],[Comp2],[Comp3],[Comp4]))u
GROUP BY Val

/*
comp       Comp1       Comp2       Comp3       Comp4
---------- ----------- ----------- ----------- -----------
DIFF       1           2           0           0
EQUAL      2           1           2           3
GAIN       1           1           1           1
LOSS       1           0           0           1

(4 row(s) affected)

Val        Comp1       Comp2       Comp3       Comp4
---------- ----------- ----------- ----------- -----------
DIFF       1           2           0           0
EQUAL      2           1           2           3
GAIN       1           1           1           1
LOSS       1           0           0           1

(4 row(s) affected)
*/

this is still missing NULL counts. see OPs reqd output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 04:50:33

Solution without UNPIVOT. Also suitable for OP's request.

SELECT Comp,
SUM(CASE WHEN C = 'Comp1' THEN 1 ELSE 0 END) AS Comp1,
SUM(CASE WHEN C = 'Comp2' THEN 1 ELSE 0 END) AS Comp2,
SUM(CASE WHEN C = 'Comp3' THEN 1 ELSE 0 END) AS Comp3,
SUM(CASE WHEN C = 'Comp4' THEN 1 ELSE 0 END) AS Comp4
FROM
(SELECT 'Comp1' AS C, ISNULL(Comp1, 'NULL') AS Comp FROM tbl
UNION ALL
SELECT 'Comp2', ISNULL(Comp2, 'NULL') FROM tbl
UNION ALL
SELECT 'Comp3', ISNULL(Comp3, 'NULL') FROM tbl
UNION ALL
SELECT 'Comp4', ISNULL(Comp4, 'NULL') FROM tbl)D
GROUP BY Comp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources