Putting a sum and a count into a pivot?

1

I have a fairly simple pivot I created for getting the sum of years put into columns. But I'd like to add the count of DistinctConsumers into the same pivot so they are displayed across the columns as wee. Is there a solution for this?
My query looks like this:

SELECT *
INTO #summaryone

FROM (
SELECT
FY
,Provider
,DistinctConsumers
,Service
,PaidAmt

FROM #Summary) AS ProviderAmts
PIVOT
(SUM(PaidAmt)
FOR FY
IN (
FY21
,FY22
,FY23
,FY24
,FY25
)
) AS PivotTable

I want my output to look like this:

image
image919×139 5.39 KB

2

You are familiar with subquerys so you can simple create 2 pivottables, one for sum(PaidAmt) and another for - I guess- sum(DistinctConsumers). You can join both tables on provider.

3

I've tried making two separate pivots then make a new table joining the two on the provider, but it just adds extra columns for the counts and the sums, and then in the FY columns it pulls in small numbers. Not sure what I'm doing wrong? I was hoping I could just go the Count and the Sum in one table but from what I've seen it's more complicated than I'm able to tackle.

4

For pivoting multiple column, it is easier to do it using conditional aggregate

Example :


SELECT Provider,
       FY21Consumers = MAX (CASE WHEN FY = 'FY21' THEN DistinctConsumers END),
       FY21PiadAmt   = SUM (CASE WHEN FY = 'FY21' THEN PaidAmt END),
       FY22Consumers = MAX (CASE WHEN FY = 'FY22' THEN DistinctConsumers END),
       FY22PiadAmt   = SUM (CASE WHEN FY = 'FY22' THEN PaidAmt END)
FROM   #Summary
GROUP BY Provider
5

hi

hope this helps

Test Data Create Script 
DROP TABLE #Summary

CREATE TABLE #Summary (
FY varchar(4) NOT NULL,
Provider varchar(50) NOT NULL,
DistinctConsumers int NOT NULL,
Service varchar(50) NOT NULL,
PaidAmt decimal(18,2) NOT NULL
);

INSERT INTO #Summary (FY, Provider, DistinctConsumers, Service, PaidAmt) VALUES
('FY21','ProviderA',120,'Consulting',15000.00),
('FY21','ProviderA',110,'Support',8000.00),
('FY21','ProviderB',95,'Consulting',12000.00),
('FY21','ProviderB',60,'Maintenance',4000.00),
('FY22','ProviderA',130,'Consulting',16000.00),
('FY22','ProviderA',115,'Support',8500.00),
('FY22','ProviderB',100,'Consulting',12500.00),
('FY22','ProviderB',70,'Maintenance',4200.00),
('FY23','ProviderA',140,'Consulting',17000.00),
('FY23','ProviderA',125,'Support',9000.00),
('FY23','ProviderB',105,'Consulting',13000.00),
('FY23','ProviderB',65,'Maintenance',4500.00),
('FY24','ProviderA',150,'Consulting',18000.00),
('FY24','ProviderA',120,'Support',9500.00),
('FY24','ProviderB',110,'Consulting',13500.00);

select * from #Summary

image
image628×438 12.1 KB

6

/* Method 1 -- Thanks harishgg1 for example data */

SELECT
	p1.[Provider],
	p1.FY21_PaidAmt,
	p2.FY21_DistinctConsumers,
	p1.FY22_PaidAmt,
	p2.FY22_DistinctConsumers,
	p1.FY23_PaidAmt,
	p2.FY23_DistinctConsumers,
	p1.FY24_PaidAmt,
	p2.FY24_DistinctConsumers,
	p1.FY25_PaidAmt,
	p2.FY25_DistinctConsumers
FROM
    (
	    SELECT 
	    'Provider' = [Provider],
	    'FY21_PaidAmt'= FY21,
	    'FY22_PaidAmt'= FY22,
	    'FY23_PaidAmt'= FY23,
	    'FY24_PaidAmt'= FY24,
	    'FY25_PaidAmt'= FY25
	    FROM (
			    SELECT
				    FY
				    ,[Provider]
				    , PaidAmt
			    FROM #Summary
		    ) AS ProviderAmts
	    PIVOT
	        (SUM(PaidAmt) FOR FY IN (FY21,FY22,FY23,FY24,FY25)) AS Pivot_PaidAmt
	) p1
JOIN
    (
	    SELECT 
	    'Provider' = [Provider],
	    'FY21_DistinctConsumers'= FY21,
	    'FY22_DistinctConsumers'= FY22,
	    'FY23_DistinctConsumers'= FY23,
	    'FY24_DistinctConsumers'= FY24,
	    'FY25_DistinctConsumers'= FY25
	    FROM (
			    SELECT
				    FY
				    ,[Provider]
				    , DistinctConsumers
			    FROM #Summary
		    ) AS ProviderAmts
	    PIVOT
	        (SUM(DistinctConsumers) FOR FY IN (FY21,FY22,FY23,FY24,FY25)) AS Pivot_DistinctConsumers
) p2 ON
P1.[Provider]=p2.[Provider]
7

hi

hope this helps

create sample data script 
-- Sample data
CREATE TABLE #Summary (
    FY VARCHAR(10),
    Provider VARCHAR(50),
    DistinctConsumers INT,
    Service VARCHAR(50),
    PaidAmt DECIMAL(18,2)
);

INSERT INTO #Summary (FY, Provider, DistinctConsumers, Service, PaidAmt)
VALUES
-- Provider A: has data in FY21, FY22, no consumers in FY23
('FY21','Provider A', 101,'Dental', 27976.16),
('FY22','Provider A', 31,'Dental', 11516.68),
('FY23','Provider A', NULL,'Dental', 0.00),

-- Provider B: consistent across all years
('FY21','Provider B', 251,'Vision', 82575.36),
('FY22','Provider B', 257,'Vision', 109256.13),
('FY23','Provider B', 246,'Vision', 99974.29),
('FY24','Provider B', 260,'Vision', 120000.00),
('FY25','Provider B', 270,'Vision', 125500.00),

-- Provider K: small counts, varying PaidAmt
('FY21','Provider K', 3,'Medical', 3959.64),
('FY22','Provider K', 5,'Medical', 9705.00),
('FY23','Provider K', 1,'Medical', 3027.96),

-- Provider Q: moderate counts, all years
('FY21','Provider Q', 52,'Dental', 20183.32),
('FY22','Provider Q', 31,'Dental', 17843.82),
('FY23','Provider Q', 26,'Dental', 20084.61),
('FY24','Provider Q', 40,'Dental', 25000.00),
('FY25','Provider Q', 45,'Dental', 30000.00);

t-sql

SELECT
    Provider,
    -- FY21
    COUNT(DISTINCT CASE WHEN FY = 'FY21' THEN DistinctConsumers END) AS FY21_consumers,
    SUM(CASE WHEN FY = 'FY21' THEN PaidAmt END) AS FY21_PaidAmt,

    -- FY22
    COUNT(DISTINCT CASE WHEN FY = 'FY22' THEN DistinctConsumers END) AS FY22_consumers,
    SUM(CASE WHEN FY = 'FY22' THEN PaidAmt END) AS FY22_PaidAmt,

    -- FY23
    COUNT(DISTINCT CASE WHEN FY = 'FY23' THEN DistinctConsumers END) AS FY23_consumers,
    SUM(CASE WHEN FY = 'FY23' THEN PaidAmt END) AS FY23_PaidAmt,

    -- FY24
    COUNT(DISTINCT CASE WHEN FY = 'FY24' THEN DistinctConsumers END) AS FY24_consumers,
    SUM(CASE WHEN FY = 'FY24' THEN PaidAmt END) AS FY24_PaidAmt,

    -- FY25
    COUNT(DISTINCT CASE WHEN FY = 'FY25' THEN DistinctConsumers END) AS FY25_consumers,
    SUM(CASE WHEN FY = 'FY25' THEN PaidAmt END) AS FY25_PaidAmt
FROM #Summary
GROUP BY Provider
ORDER BY Provider;

Output

image
image1594×208 20.9 KB