February 23, 2011 at 9:09 am
I need to capture counts of records in a table by values in columns. For a table that has say 8 different INDICATOR columns - I need a report that lists counts by each column in a waterfall manner
Count of records where ind1 = 1
Count of records where ind1 = 1 and ind2 =1
Count of records where ind1 = 1 and ind2 =1 and ind3 = 1
and so on.....
One way is to write multiple sqls like above adding a new where claus each time. That will require running it multiple times and will be time consuming - Is there a way to do it in one SQL? Here is a sample table creation SQL and what is required as output from it.
create table ALL_INDS (
IDINT,
IND1BIT,
IND2BIT,
IND3BIT,
IND4BIT,
IND5BIT,
IND6BIT,
IND7BIT,
IND8BIT)
--TRUNCATE TABLE ALL_INDS
DECLARE @RECS SMALLINT
SET @RECS = 1
WHILE @RECS < 21
BEGIN
INSERT INTO ALL_INDS VALUES(@RECS, 0, 0, 0, 0, 0, 0, 0, 0)
SET @RECS = @RECS + 1
END
--SELECT * FROM ALL_INDS
UPDATE ALL_INDS
SET IND1 = 1
WHERE ID IN (1, 2, 3, 5, 6, 7, 8, 9, 11, 12, 13, 16, 18, 19, 20)
UPDATE ALL_INDS
SET IND2 = 1
WHERE ID IN (1, 2, 5, 4, 6, 7, 8, 9, 10, 11, 12, 16, 18, 19, 20)
UPDATE ALL_INDS
SET IND3 = 1
WHERE ID IN (1, 2, 3, 5, 6, 7, 8, 12, 13, 16, 19, 20)
UPDATE ALL_INDS
SET IND4 = 1
WHERE ID IN (1, 2, 5, 4, 6, 7, 8, 9, 10, 12, 16, 20)
UPDATE ALL_INDS
SET IND5 = 1
WHERE ID IN (1, 2, 3, 5, 6, 7, 8, 11, 14, 15, 18, 20)
UPDATE ALL_INDS
SET IND6 = 1
WHERE ID IN (1, 4, 6, 7, 8, 9, 10, 12, 13, 16, 17, 20)
UPDATE ALL_INDS
SET IND7 = 1
WHERE ID IN (1, 2, 3, 5, 6, 7, 8, 9, 11, 13, 15)
UPDATE ALL_INDS
SET IND8 = 1
WHERE ID IN (1, 8)
SELECT COUNT(*) FROM ALL_INDS
WHERE IND1 = 1--15
AND IND2 = 1--13
AND IND3 = 1--10
AND IND4 = 1--9
AND IND5 = 1--7
AND IND6 = 1--5
AND IND7 = 1--4
AND IND8 = 1--2
I need a report that lists the counts by each ind - like BELOW
Is there a way it can be done in one SQL - rather than running the same code multiple
times ading a new where clause in each run like it is done above.
IND1IND2IND3IND4IND5IND6IND7IND8
15131097542
I will really appreciate help to learn a new nice trick to do this in a single SQL. Thanks
February 23, 2011 at 10:11 am
SELECT SUM(1 & IND1) AS IND1
,SUM(1 & IND1 & IND2) AS IND2
,SUM(1 & IND1 & IND2 & IND3) AS IND3
-- etc
FROM ALL_INDS
February 23, 2011 at 10:20 am
WITH INDS AS (
SELECT CAST(IND1 AS int) AS IND1,
CAST(IND2 AS int) AS IND2,
CAST(IND3 AS int) AS IND3,
CAST(IND4 AS int) AS IND4,
CAST(IND5 AS int) AS IND5,
CAST(IND6 AS int) AS IND6,
CAST(IND7 AS int) AS IND7,
CAST(IND8 AS int) AS IND8
FROM ALL_INDS
)
SELECT SUM(IND1) AS IND1,
SUM(IND1 & IND2) AS IND2,
SUM(IND1 & IND2 & IND3) AS IND3,
SUM(IND1 & IND2 & IND3 & IND4) AS IND4,
SUM(IND1 & IND2 & IND3 & IND4 & IND5) AS IND5,
SUM(IND1 & IND2 & IND3 & IND4 & IND5 & IND6) AS IND6,
SUM(IND1 & IND2 & IND3 & IND4 & IND5 & IND6 & IND7) AS IND7,
SUM(IND1 & IND2 & IND3 & IND4 & IND5 & IND6 & IND7 & IND8) AS IND8
FROM INDS
-- Gianluca Sartori
February 23, 2011 at 10:22 am
Thanks - I understand this solution... Actually my bad - I simplified the situation.
In the real table - the columns are not really bit columns - those are various kinds - like STATE_cd (2 byte char), ratings (one byte char)
So my waterfall report is supposed to be
count of emploees - WHere all IDS
then where STATE is limited to a few states
then CRedit is imited to a few types
then salary is > some value.....
Etc etc...
Thanks
February 23, 2011 at 10:26 am
Then use a case statement inside the SUM to return 1 or 0 depending a whether the condition is met.
February 23, 2011 at 10:29 am
Alright - thanks. Will try that.
February 23, 2011 at 10:31 am
I'm afraid it's overcomplicating a simple problem, anyway you could also try this:
;WITH FlagCounts AS (
SELECT IND1, IND2, IND3, IND4, IND5, IND6, IND7, IND8, COUNT(*) AS CNT,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN -- PRESERVE ROLLUP ORDER
FROM ALL_INDS
GROUP BY IND1, IND2, IND3, IND4, IND5, IND6, IND7, IND8
WITH ROLLUP
)
SELECT *
FROM (
-- EXLUDE LAST ROLLUP ROW (ALL NULLS)
SELECT TOP(8) ColName = 'IND' + CAST(IND AS varchar(2)), CNT
FROM (
-- LAST 9 ROWS CONTAIN ROLLUP ROWS
SELECT TOP(9) *,
IND = ROW_NUMBER() OVER(ORDER BY RN DESC) - 1
FROM FlagCounts
ORDER BY RN DESC
) AS T9
ORDER BY RN ASC
) AS SRC
PIVOT (MIN(CNT) FOR ColName IN([IND1],[IND2],[IND3],[IND4],[IND5],[IND6],[IND7],[IND8])) AS pvt
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply