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.