Returning Record Count in Pivot Table

I have a stored procedure which returns data using a PIVOT statement. I need to modify the statement so it returns a record count along with the row of data but am not able to comprehend the concept. Attached is an example of what I have. It returns data like so:

EquipmentGroup              Urgent     Routine
Radars                                      100.00        75.00
Radios                                       50.00          75.00

What I need now is an output which sums up the number or requisitions that make up each EquipmentGroup by Priority like so:
EquipmentGroup              Urgent    #UrgentReqs     Routine    #RoutineReqs
Radars                                      100.00           3                   75.00           4
Radios                                       50.00             2                   75.00           2

I have played around with what I have quite a bit but am missing some key concept. Any help would be appreciated.
                 
SELECT	EquipmentGroup, 
		ISNULL(U, 0) AS Urgent,  
		ISNULL(R, 0) AS Routine
		
		
FROM
(
    SELECT			
		r.ImmsEquipmentGroup,
		r.Priority, 
		Sum(r.TOTAL_EST_REQ_AMT) AS Price,
		
	FROM
		Requisitions			r
		
	GROUP BY EquipmentGroup,r.Priority
) Data
PIVOT (SUM(Price) FOR Priority IN(U,R)) pvt
ORDER BY EquipmentGroup

Open in new window

LVL 6
AkAlanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
Hi,

I got this to work by using a second pivot clause.

I'm not sure that it is the most efficient solution, but on the limited test data from your previous question it works.

Regards
  David
use tempdb
go

if object_id( N'tempdb..#Equip', N'U' ) is not null 
	drop table #Equip;
	
create table #Equip(
	EquipmentGroup varchar( 10 )
	, Priority char( 1 )
	, Price money
	)
;
	
insert #Equip(
	EquipmentGroup
	, Priority
	, Price
	)
	values(
		'Radar', 'E', 50 )
		, ( 'Radar', 'R', 25 )
		, ( 'Radar', 'R', 25 )
		, ( 'Radio', 'E', 75 )
		, ( 'Radio', 'R', 25 )
;

select *
from #Equip
;

select 
	pvt.EquipmentGroup
	, pvt.E
	, pvt.R
	, c.E
	, c.R
from (
	select 
		EquipmentGroup
		, Priority
		, Price
	from #Equip
	) e
	pivot(
		sum( Price )
		for Priority in (
			[E], [R]
			) 
	) as pvt
inner join (
	select 
		EquipmentGroup
		, Priority
		, Price
	from #Equip
	) e
	pivot(
		count( Price )
		for Priority in (
			[E], [R]
			) 
	) as c
	on c.EquipmentGroup = pvt.EquipmentGroup
order by
	pvt.EquipmentGroup
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
PS You might want to add the two counts together instead of displaying them separately.

Change
select
      pvt.EquipmentGroup
      , pvt.E
      , pvt.R
      , c.E
      , c.R

to
select
      pvt.EquipmentGroup
      , pvt.E
      , pvt.R
      , c.E + c.R


AhmedHindyCommented:
try this

 
SELECT 
    pk.EquipmentGroup,
    ISNULL(pv.U,0)as Urgent,
    pk.R AS [#UrgentReqs]     ,
    ISNULL(pv.R,0) as Routine,
        pk.U AS [#RoutineReqs] 

 FROM
 (SELECT  EquipmentGroup,   
          Priority ,
          SUM(TOTAL_EST_REQ_AMT) AS Price
  FROM  Requisitions group by EquipmentGroup, Priority) p
          PIVOT (SUM(Price) FOR Priority in (U, R)) as  pv
  INNER JOIN         
 (SELECT  DISTINCT EquipmentGroup,   
                    Priority   ,
                    TOTAL_EST_REQ_AMT
   FROM  Requisitions) p
            PIVOT ( COUNT (TOTAL_EST_REQ_AMT) FOR Priority in (U, R)) as  pk

Open in new window

SharathData EngineerCommented:
Can you provide some sample data from your table?
AkAlanAuthor Commented:
Worked perfectly thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.