SQL sum function - how to sum up and still include the original values

Transact-SQL
1

I want to roll up data (sum the numeric columns) and still include the other columns original values. Basically I'm trying to roll up the "detail" rows to "master" rows retaining the NON-summed values that are in the "detail" rows.

Input is 3,213 rows which are made up of 63 sets of 51.
63 unique PortIndex's and 51 unique StartDates (which includes the hh:mm:ss:mss portion).

An example of the 3,213 rows, I'm showing the first 3 of the 1st set of 63 and then the 1st 3 of the 2nd set of 63 rows. Note the StartDate and the EndDate are the SAME for each set of 63.

S P sum1 sum2 sum3 sum4 StartDate               EndDate                 D
8 1  2184 821  0.00 0.00 2016-08-29 17:00:30.810 2016-08-29 17:01:00.827 day
8 2  0    2    0.00 0.00 2016-08-29 17:00:30.810 2016-08-29 17:01:00.827 day
8 3  621  598  0.00 0.00 2016-08-29 17:00:30.810 2016-08-29 17:01:00.827 day

8 1 5000 300  1.00 2.00 2016-08-29 17:01:30.847 2016-08-29 17:02:00.873 day
8 2 15   2    4.00 3.00 2016-08-29 17:01:30.847 2016-08-29 17:02:00.873 day
8 3 250  222  0.00 0.00 2016-08-29 17:01:30.847 2016-08-29 17:02:00.873 day

My expected result is 63 output rows - 1 "master" row for each set of the 63 "detail" rows. Summed up columns sum1, sum2, sum3 and sum4 and retaining the values in columns s, p, StartDate, EndDate and D. But I want the StartDate to be the 1st rows value and the EndDate to be the last rows [the 51st] value.

I sum (roll up) the data per PortIndex for a given "day" - just the yyyymmdd portion - so I exclude the hh:mm:ss:mss portion in the GROUP BY. I do subqueries to get the 1st and last dates accordingly.

My query is:

SELECT SwitchID as S,
PortIndex as P,
SUM(BandwidthIn) as Sum1,
SUM(BandwidthOut) as Sum2,
SUM(BandwidthInMbps) as Sum3,
SUM(BandwidthOutMbps) as Sum4,
( SELECT Top 1 sub_bandwidth.StartDate
FROM dbo.BandwidthLogCalculatedTest sub_bandwidth
WHERE main_bandwidth.PortIndex = sub_bandwidth.PortIndex
ORDER BY main_bandwidth.StartDate),
( SELECT Top 1 sub_bandwidth.EndDate
FROM dbo.BandwidthLogCalculatedTest sub_bandwidth
WHERE main_bandwidth.PortIndex = sub_bandwidth.PortIndex
ORDER BY main_bandwidth.EndDate DESC), -- 'Order by Desc' gets the last one in the set.
'day' as D
FROM dbo.BandwidthLogCalculatedTest main_bandwidth
WHERE ( main_bandwidth.StartDate < CAST(DATEADD(DAY, -1, GETDATE()) AS DATETIME) )
AND (main_bandwidth.EntryType = 'Second' )
GROUP BY main_bandwidth.SwitchID,
main_bandwidth.PortIndex,
DATEADD(dd, DATEDIFF(dd, 0, main_bandwidth.StartDate), 0),
DATEADD(dd, DATEDIFF(dd, 0, main_bandwidth.EndDate), 0)

I get:
Column 'dbo.BandwidthLogCalculatedTest.StartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How do I get the Group by to work properly?

2

Since

you could just change your group by section in your first query:

SELECT SwitchID as S,
   PortIndex as P,
   SUM(BandwidthIn) as Sum1,
   SUM(BandwidthOut) as Sum2,
   SUM(BandwidthInMbps) as Sum3,
   SUM(BandwidthOutMbps) as Sum4,
   StartDate,  
   EndDate,
   'day' as D
FROM dbo.BandwidthLogCalculatedTest
WHERE ( StartDate < CAST(DATEADD(DAY, -1, GETDATE()) AS DATETIME) )
AND (EntryType = 'Second' )
GROUP BY SwitchID,
         PortIndex,
         StartDate,
         EndDate
3

Thanks..but I had to change my problem descriptions so your answer is not likely valid anymore.

4

Use the min and max functions

SELECT SwitchID as S,
 PortIndex as P,
 SUM(BandwidthIn) as Sum1,
 SUM(BandwidthOut) as Sum2,
 SUM(BandwidthInMbps) as Sum3,
 SUM(BandwidthOutMbps) as Sum4,
 MIN(StartDate) as StartDate,
 MAX(EndDate) as EndDate,
 'day' as D
FROM dbo.BandwidthLogCalculatedTest
WHERE StartDate < CAST(DATEADD(DAY, -1, GETDATE()) AS DATETIME)
AND EntryType = 'Second'
GROUP BY SwitchID,
 PortIndex
1 Like
5

Thanks so much...that did it.