Link to home
Start Free TrialLog in
Avatar of AkAlan
AkAlan

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


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

Can you provide some sample data from your table?
Avatar of AkAlan
AkAlan

ASKER

Worked perfectly thanks