SQLTeam.com | Weblogs | Forums

Problem with aggregates and Group By

sql2014

#1

Getting this error
Column '#BandwidthLogCalculatedEach24Summed.BandwidthLogCalculatedId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Temp tables:

CREATE TABLE #BandwidthLogCalculatedAll24Hours (
        BandwidthLogCalculatedId               int,
	SwitchID                               int,
	PortIndex                              int,
	BandwidthIn			       bigint,
	BandwidthOut		               bigint,
	BandwidthInMbps		               decimal(7,2),
	BandwidthOutMbps	               decimal(7,2),
	StartDate  			       datetime,
	EndDate			               datetime,
	EntryType                              varchar(25),
	HourNumber                             int ) -- will initially contain 99.

CREATE TABLE #BandwidthLogCalculatedEach24Summed (
        BandwidthLogCalculatedId           int,
	SwitchID                               int,
	PortIndex                              int,
	BandwidthIn			       bigint,
	BandwidthOut		               bigint,
	BandwidthInMbps		               decimal(7,2),
	BandwidthOutMbps	               decimal(7,2),
	StartDate  			       datetime,
	EndDate			               datetime,
	EntryType                              varchar(25),
	HourNumber                             int )  -- to designate the hour 'set'.

INSERT INTO #BandwidthLogCalculatedEach24Summed (
        BandwidthLogCalculatedId,
	SwitchID,
	PortIndex,
	BandwidthIn,
	BandwidthOut,
	BandwidthInMbps,
	BandwidthOutMbps,
	StartDate,
	EndDate,
	EntryType,
	HourNumber )
SELECT 	BandwidthLogCalculatedId,
        SwitchID,
	PortIndex,
        SUM(BandwidthIn),
        SUM(BandwidthOut),
        AVG(BandwidthInMbps),
        AVG(BandwidthOutMbps),
	'2099-12-31 00:00:00.000',            -- Non-valid dates.
	'2099-12-31 00:00:00.000',            -- Non-valid dates.
	EntryType,
	HourNumber
FROM #BandwidthLogCalculatedAll24Hours
WHERE ( StartDate >= @StartRangeDateTime AND StartDate <= @EndRangeDateTime ) 
GROUP BY BandwidthLogCalculatedId,
         SwitchID, 
         PortIndex, 
	 EntryType,
	 HourNumber

#2

Can you post the entire error message and the full queries ?
Because , from what you posted, every thing looks fine. And tested in SSMS is running with no error.