SQL ROLLUP - SQL Server Forums

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

SQL ROLLUP

Author

Topic

Cal88
Starting Member

3 Posts

Posted - 2014-03-01 : 17:21:54

I have a question regarding ROLLUP and how to use the operator.

I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:

SELECT ty.Description AS TransportDescription,
t.SerialNumber AS Transport,
ty.Manufacturer,
SUM (r.RepairCost) AS RepairCost,
YEAR(r.EndWorkDate) AS [Year]
FROM Repair AS r
JOIN Transport AS t ON r.TransportNumber = t.TransportNumber
JOIN TransportType AS ty ON t.TransportTypeID = ty.TransportTypeID
GROUP BY ty.TransportTypeID,
ty.Description,
t.SerialNumber,
ty.Manufacturer,
YEAR(r.EndWorkDate)

How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-02 : 09:15:44

[code]
SELECT ty.Description AS TransportDescription,
t.SerialNumber AS Transport,
ty.Manufacturer,
SUM (r.RepairCost) AS RepairCost,
YEAR(r.EndWorkDate) AS [Year]
FROM Repair AS r
JOIN Transport AS t ON r.TransportNumber = t.TransportNumber
JOIN TransportType AS ty ON t.TransportTypeID = ty.TransportTypeID
GROUP BY ty.TransportTypeID,
ty.Description,
t.SerialNumber,
ty.Manufacturer,
YEAR(r.EndWorkDate)
WITH ROLLUP
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources