SQL Server SQL - Create multiple columns for fill rates based on conditions, with GROUP BY

h7wcgrx3 于 2023-03-07 发布在其他

关注(0)|答案(3)|浏览(153)

Some example data:

CREATE TABLE #TempTable (

ID varchar(10),
GroupCD varchar(10),
SurrogateKEY1 varchar(10),
SurrogateKEY2 varchar(10)

)

INSERT INTO #TempTable (ID, GroupCD, SurrogateKEY1, SurrogateKEY2)
VALUES 
    ('1', 'UNK', '12345', '89225'),
    ('3', 'ABC', NULL, '44658'),
    ('3', 'DEF', NULL, '99658'),
    ('5', 'ABC', '09184', NULL),
    ('4', 'DEF', NULL, '85598'),
    ('1', 'GHI', '80642', '77890')

ID	GroupCD	SurrogateKEY1	SurrogateKEY2
1	UNK	12345	89225
3	ABC	NULL	44658
3	DEF	NULL	99658
5	ABC	09184	NULL
4	DEF	NULL	85598
1	GHI	80642	77890

I would like to calculate the fill rates for the SurrogateKey columns, grouped by the IDs, which I have code for:

SELECT
    ID,
    CAST(SUM(CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR,
    CAST(SUM(CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR
FROM #TempTable
GROUP BY ID

ID	SurrogateKEY1_fR	SurrogateKEY2_fR
1	100.000	100.000
3	0.000	100.000
4	0.000	100.000
5	100.000	0.000

I'd like to expand this query so the fill rate calculation will be based on the GroupCD = 'ABC' or every other code.

I would like the output to be:
| ID | SurrogateKEY1_fR_NonABC | SurrogateKEY1_fR_ClassFilterABC | SurrogateKEY2_fR_NonABC | SurrogateKEY2_fR_ClassFilterABC |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 100.000 | 0.000 | 100.000 | 0.000 |
| 3 | 0.000 | 0.000 | 100.000 | 100.000 |
| 4 | 0.000 | 0.000 | 100.000 | 0.000 |
| 5 | 0.000 | 100.000 | 0.000 | 0.000 |

Does anybody know how to leverage CASE statements or another avenue in order to produce this output?

sql-server

来源：https://stackoverflow.com/questions/75631539/sql-create-multiple-columns-for-fill-rates-based-on-conditions-with-group-by

3条答案

按热度按时间

pu3pd22g1#

Its can be done this way :

SELECT
    ID,
    CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_NonABC,
    CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_ClassFilterABC,
    CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_NonABC,
    CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID

The result is a little bit different that you expected :

Demo here

赞(0）回复(0）举报 2023-03-07

nwlqm0z12#

I'd suggest to use CTE and PIVOT .

;WITH CTE AS
( 
 SELECT ID,
   GrpKey = CASE WHEN GroupCD = 'ABC' THEN GroupCD ELSE 'NonABC' END,
   SurrogateKEY1fr = CASE WHEN SurrogateKEY1 IS NULL THEN 0.0 ELSE 1.0 END,
   SurrogateKEY2fr = CASE WHEN SurrogateKEY2 IS NULL THEN 0.0 ELSE 1.0 END
 FROM TempTable
),
P1 AS
(
 SELECT ID, [ABC] AS ABCKEY1fr, [NonABC] AS NonABCKEY1fr
 FROM CTE AS DT
 PIVOT(MAX(SurrogateKEY1fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
),
P2 AS
(
SELECT ID, [ABC] AS ABCKEY2fr, [NonABC] AS NonABCKEY2fr
 FROM CTE AS DT
 PIVOT(MAX(SurrogateKEY2fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
) 
SELECT P1.ID, COALESCE(P1.ABCKEY1fr, 0.0) * 100.0 AS ABCKEY1fr, 
  COALESCE(P1.NonABCKEY1fr, 0.0) * 100.0 AS NonABCKEY1fr, 
  COALESCE(P2.ABCKEY2fr, 0.0) * 100.0 AS ABCKEY2fr,
  COALESCE(P2.NonABCKEY2fr, 0.0) * 100.0 AS NonABCKEY2fr
FROM P1
INNER JOIN P2
  ON P2.ID = P1.ID;

SQL Fiddle

赞(0）回复(0）举报 2023-03-07

oaxa6hgo3#

There is no need to divide the results of 2 aggregate COUNT() s.
You can simplify the conditional aggregation with AVG() aggregate function:

SELECT ID,
       100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_NonABC,    
       100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_ClassFilterABC, 
       100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_NonABC,    
       100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_ClassFilterABC 
FROM #TempTable
GROUP BY ID;

See the demo .

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server SQL - Create multiple columns for fill rates based on conditions, with GROUP BY

3条答案

相关问题

热门标签

最新问答