Help with summary/pivot of data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with summary/pivot of data

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-14 : 10:55:46

I'm trying to create a summary/pivot result from data in our inventory. I want to see Machine_Type and Machine_Size as rows down the left for each change in Machine_Size, then columns for count of Fleet_no, sum of 'Weekly_Rate' and average of 'Weekly_Rate'. How can I do this in SQL? My select query is below:

Select  iv.cat [Machine_Type], tm.Machine_Size [Machine_Size], di.item [Fleet_No],  di.weekchg [Weekly_Rate] from deltickitem di 
 
left join inventory iv on iv.item = di.item
left join MCSReports.tblMLookup tm on tm.Subcategory = iv.sub

where (di.stop_rent  is null or di.stop_rent > GETDATE()-5) and di.orig_start_rent <= GETDATE() and di.stage < 16 
and iv.cat not in ('ATTACH','AUGER','BREAK','BUCKET','CAR', 'COMP', 'CROSSHIRE','FORKS','GATOR', 'MISC','ROTOATT', 'TIPPER', 'TRAIL') and iv.sub <> '360BUCKET'

Many thanks
Martyn

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-14 : 11:02:15

Just a summary?

SELECT iv.cat [Machine_Type], tm.Machine_Size [Machine_Size], COUNT(di.item) [Fleet_No], AVG(di.weekchg) [Weekly_Rate_avg] , SUM(di.weekchg) [Weekly_Rate_Sum]
from deltickitem di
left join inventory iv on iv.item = di.item
left join MCSReports.tblMLookup tm on tm.Subcategory = iv.sub
WHERE (di.stop_rent is null or di.stop_rent > GETDATE()-5) and di.orig_start_rent <= GETDATE() and di.stage < 16
and iv.cat not in ('ATTACH','AUGER','BREAK','BUCKET','CAR', 'COMP', 'CROSSHIRE','FORKS','GATOR', 'MISC','ROTOATT', 'TIPPER', 'TRAIL') and iv.sub <> '360BUCKET'
GROUP BY iv.cat, tm.Machine_Size

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-14 : 11:43:10

If that would do it, yes. I just want to see for each change in Machine Size (some Machine Types will not have sizes), the values as above.

Thanks
Martyn

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-14 : 12:13:30

Yes, perfect. Thanks very much for your help.

Martyn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources