sum - union

Question

Post reply

sum - union

tn_350z

SSC Journeyman

Points: 83
More actions
November 9, 2010 at 7:45 am

#388193

Okay so I have writing this SQL query and I am trying to pull the total sum of 3 selected rows as a Total: XXXX at bottom of report. I be more then happy to answer any question the query works and i see everything i want see i just need to figure out way to get the total sum at the bottom 😛
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Interfaces.FullName AS Full_Name,
Interfaces.InterfaceName AS Interface_Name,
SUM((NullIf(In_TotalBytes,-2)+NullIf(Out_TotalBytes,-2))) AS SUM_of_TotalBytesRecvXmit,
SUM(InterfaceTraffic.In_TotalBytes) AS SUM_of_Total_Bytes_Received,
SUM(InterfaceTraffic.Out_TotalBytes) AS SUM_of_Total_Bytes_Transmitted
FROM
Interfaces INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
WHERE
( DateTime BETWEEN 40458 AND 40489 )
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Interfaces.FullName, Interfaces.InterfaceName
ORDER BY SummaryDate ASC, 3 ASC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

You can use WITH ROLLUP.

For example:

USE AdventureWorks2008R2;

GO

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'

FROM Sales.SalesPerson

GROUP BY SalesQuota WITH ROLLUP;

GO

Thanks

tn_350z SSC Journeyman Points: 83 More actions · Answer 2

I am sorry, trying to understand how that would work....could you give me little more detail?

by the way thank you for quick respond its greatly appreciated

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

tn_350z (11/9/2010)
I am sorry, trying to understand how that would work....could you give me little more detail?
by the way thank you for quick respond its greatly appreciated

Please refer: http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx

Thanks