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?
 
David ToddSenior DBACommented:
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

0
 
David ToddSenior DBACommented:
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


0
 
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

0
 
SharathData EngineerCommented:
Can you provide some sample data from your table?
0
 
AkAlanAuthor Commented:
Worked perfectly thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.