Grouping Set in SQL Server 2008– Another example


—SQL Server 2008

create table Transport (id int identity(1,1), TransporterId int,

VehicleNo varchar(50), PetrolConsumed int, TotalCost int)

 

insert into Transport values (1,’ABC-123′,15,900)

, (2,’XYZ-654′, 20,1200)

, (1,’MNB-592′,18,1080)

, (2,’XYZ-654′,15,900 )

, (3,’PQR-604′,20,1200)

SELECT TransporterId,Isnull(VehicleNo, ‘Total—–‘)AS VehicleNo

,Sum(PetrolConsumed) AS PetrolConsumed

,Sum(TotalCost) AS TotalCost

FROM Transport

GROUP BY grouping sets( ( TransporterId, VehicleNo, PetrolConsumed, TotalCost ), ( TransporterId ) )

Order By TransporterId

drop table transport

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s