T-SQL Count question

Question

I have the following table that I need to summarize

ID      A   B   C   D   E   F   G
----------------------------------
1-100   1   2   1   1   1   1   1
1-201   1   2   1   2   2   2   2
1-322   1   1   1   1   2   2   1
2-155   1   1   2   1   1   2   2
2-167   2   1   2   1   2   1   2
2-389   2   2   1   2   1   1   2
2-423   1   2   2   2   1   1   1
3-10    2   1   1   1   2   2   2
3-222   1   1   1   1   2   2   1
3-397   2   1   1   2   2   1   1

In the table above, the values 1 is coded as S while 2 is coded as R. Also, the ID is a code as XX, YY or XX where the digit before the - represents XX, YY, or XX.

The summary I would like to have is this

         XX         YY              ZZ
------------------------------------------
A   S   3   100%    2   50%     1   33%
    R   0   0%      2   50%     2   66%
B   S   2   66%     2   50%     3   100%
    R   1   33%     2   50%     0   0%
C   S   3   100%    3   75%     3   100%
    R   0   0%      1   25%     0   0%
D   S   2   66%     2   50%     2   66%
    R   1   33%     2   50%     1   33%
E   S   1   33%     3   75%     0   0%
    R   2   66%     1   25%     3   100%
F   S   1   33%     3   75%     2   66%
    R   2   66%     1   25%     1   33%
G   S   2   66%     1   25%     1   33%
    R   1   33%     3   75%     2   66%

So I need to rotate the table, count the 1/2 and create percentages.

This has got me quite puzzled and I have gone down a few dead ends on how to do this (let alone how to do it elegantly)

Thanks in advance!

With Martin's help I am ever so close. My data is of course a bit goofier than the example I gave so I am still having difficulties. I have censured the data as well as put the proper codings in that I want - yeah the codings are real goofy, I have no control over them :)

I have extended Martins SQL to link to my data but there are two remaining issues. The order of the rows in the Thing Column is not quite what I want.

When I try the following code, I get a "Must declare the scalar variable @order" - it does not like joining to my temp table called myOrder.

DECLARE @myOrder TABLE (rug varchar(3), rugOrder int)    
INSERT @myOrder
    SELECT 'INH', 1 UNION ALL
    SELECT 'RIF', 2 UNION ALL
    SELECT 'KM', 3 UNION ALL
    SELECT 'AK', 4 UNION ALL
    SELECT 'CM', 5 UNION ALL
    SELECT 'MOX', 6 UNION ALL
    SELECT 'OFX', 7;

WITH YourData(ID, INH, RIF, KM, AK, CM, MOX, OFX) As
(SELECT Sample_ID, INH, RIF, KM, AK, CM, MOX, OFX
FROM dbo.[GCT_Rug] WHERE Sample_ID NOT LIKE '99%')

, Unpivoted AS
(
SELECT S_R_Flag,
       Thing,
       Site = 
        CASE 
        WHEN LEFT(ID,1) = 1 THEN 1 
        WHEN LEFT(ID,1) = 6 THEN 1 
        WHEN LEFT(ID,1) = 8 THEN 2 
        WHEN LEFT(ID,1) = 9 THEN 3 END

FROM YourData
UNPIVOT
   (S_R_Flag FOR Thing IN (INH, RIF, KM, AK, CM, MOX, OFX)
)AS unpvt)
SELECT Thing
       ,SRFLAG =
            CASE 
                WHEN S_R_Flag = 1 THEN 'S'
                WHEN S_R_Flag = 2 THEN 'R'
            END
       ,[1] AS IND
   ,round(CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0)*100,1) AS 'Ind Percent'
       ,[2] AS MD
   ,round(CAST([2] AS FLOAT) / NULLIF(SUM([2]) OVER (PARTITION BY Thing),0)*100,1) AS 'MD Percent'
   ,[3] AS 'SA'
   ,round(CAST([3] AS FLOAT) / NULLIF(SUM([3]) OVER (PARTITION BY Thing),0)*100,1) AS 'SA Percent'

FROM Unpivoted 
INNER JOIN @myOrder
ON Unpivoted.Thing= @myOrder.rug
PIVOT (COUNT (Site) FOR Site IN ( [1], [2], [3])) AS pvt
ORDER BY rugOrder,
         SRFLAG;

What does the error "Must declare the scalar variable @myOrder" mean and why can't I join to it ?

Thanks again you guys (especially Martin) are awesome !

So far I've been going down this route SUM(CASE WHEN A=1 THEN 1 ELSE 0 END) AS AS, SUM(CASE WHEN A=2 THEN 1 ELSE 0 END) AS AB — user918967, Sep 23 '11 at 15:52
I don't have a complete query. As I fiddled with pushing the data around, it quickly appeared to me that my approach would not be successful. I am still trying to figure out the best approach... I was thinking of moving the data into a format where I had three columns ID, COLUMNABCDEFG, and Data where the data in COLUMNABCDEFG would be A, B, C, D, E, F, or G and Data would be 1 or 2. Then I could use a COUNT aggregation ... — user918967, Sep 23 '11 at 17:03

score 3 · Accepted Answer · answered Sep 23 '11 at 16:14

3

This essentially gives you the results you need though I haven't bothered mapping the numeric values to the codes

;WITH YourData(ID,A,B,C,D,E,F,G) As
(
SELECT '1-100',1,2,1,1,1,1,1 UNION ALL
SELECT '1-201',1,2,1,2,2,2,2 UNION ALL
SELECT '1-322',1,1,1,1,2,2,1 UNION ALL
SELECT '2-155',1,1,2,1,1,2,2 UNION ALL
SELECT '2-167',2,1,2,1,2,1,2 UNION ALL
SELECT '2-389',2,2,1,2,1,1,2 UNION ALL
SELECT '2-423',1,2,2,2,1,1,1 UNION ALL
SELECT '3-10 ',2,1,1,1,2,2,2 UNION ALL
SELECT '3-222',1,1,1,1,2,2,1 UNION ALL
SELECT '3-397',2,1,1,2,2,1,1
), Unpivoted AS
(
SELECT S_R_Flag,
       Thing,
       SUBSTRING(ID,1,CHARINDEX('-',ID )-1) AS Code,ID
FROM YourData
UNPIVOT
   (S_R_Flag FOR Thing IN (A,B,C,D,E,F,G)
)AS unpvt)
SELECT Thing
       ,S_R_Flag
       ,[1]
       ,CAST([1] AS FLOAT) / SUM([1]) OVER (PARTITION BY Thing)
       ,[2]
       ,CAST([2] AS FLOAT) / SUM([2]) OVER (PARTITION BY Thing)
       ,[3]
       ,CAST([3] AS FLOAT) / SUM([3]) OVER (PARTITION BY Thing)
FROM Unpivoted
PIVOT (COUNT (ID) FOR Code IN ( [1], [2], [3] )) AS pvt
ORDER BY Thing,
         S_R_Flag;

answered Sep 23 '11 at 16:14

Martin Smith

438,706
87
741
845

can this be more generalized ? The table grows over time (ID) so I do not know the number of rows or the pattern of 1&2s. – user918967 Sep 23 '11 at 17:00
1

@user918967 - Not without dynamic SQL. Both `PIVOT` and `UNPIVOT` need static column lists [unless it is possible to adjust this XML technique to work](http://stackoverflow.com/questions/7341143/flattening-of-a-1-row-table-into-a-key-value-pair-table/7343619#7343619) – Martin Smith Sep 23 '11 at 17:03
Oh I think my misunderstanding came from the WITH statement. I figured that bit out I think but apparently my real data has some zeros in it and T-SQL throws an error "Divide by zero error encountered" So I guess I need another check in the statement so the division is not zero. How would that IF THEN statement be formatted ? – user918967 Sep 23 '11 at 17:22
@user918967 - Ah right. See what you mean. Replace `SUM([1]) OVER (PARTITION BY Thing)` etc with `NULLIF(SUM([1]) OVER (PARTITION BY Thing),0)`. That's the only places with `/` in the code. – Martin Smith Sep 23 '11 at 17:40
Hi Martin, CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0) must be real close but now everything is 0 with the percentages as NULL. It looks like the Sum[1] is not equal to 0 and thus it says NULL ? – user918967 Sep 23 '11 at 20:47
@user918967 - Can you (using my answer as a base) amend the example data sufficient to demonstrate the divide by zero problem and post that into your question? – Martin Smith Sep 23 '11 at 21:35
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3742/discussion-between-user918967-and-martin-smith) – user918967 Sep 24 '11 at 04:22
1

@user918967 RE: the code you posted you need to give the table variable an alias. Use `INNER JOIN @myOrder [@myOrder] ON Unpivoted.Thing= [@myOrder].rug` – Martin Smith Sep 24 '11 at 08:09

T-SQL Count question

1 Answers1