CODEWITHSUNDEEP
sql-servergroup-by
amulllb
amulllb

Reputation: 3166

ms sql server group by max

The table InterfaceTraffic_detail (from Solarwinds SQL Server) basically has a table structure:

CREATE TABLE [dbo].[InterfaceTraffic_Detail](
    [InterfaceID] [int] NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [In_Maxbps] [real] NULL,
    [Out_Maxbps] [real] NULL
 CONSTRAINT [PK_InterfaceTraffic_Detail] PRIMARY KEY CLUSTERED 
(
    [InterfaceID] ASC,
    [DateTime] ASC,
    [Archive] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

Data:

InterfaceID  Out_Maxbps  In_Maxbps    DateTime
------------|-----------|------------|-------------------------|
1           | 10        | 10         | 2015-06-23 01:03
1           | 11        | 11         | 2015-06-23 01:08
2           | 12        | 12         | 2015-06-23 01:05
2           | 10        | 10         | 2015-06-23 01:10

How can I group by InterfaceID?

select 
     max(Out_Maxbps) over (partition by itt.InterfaceID),
     avg(Out_Maxbps) over (partition by itt.InterfaceID),
     max(In_Maxbps) over (partition by itt.InterfaceID),
     avg(In_Maxbps) over (partition by itt.InterfaceID),
     itt.InterfaceID, 
     itt.datetime
from InterfaceTraffic_detail as itt with (nolock)
where (itt.DateTime >= '2015-06-23 01:00:00'
     and itt.DateTime <= '2015-06-23 05:00:00'
     and (itt.InterfaceID = 489108 or itt.InterfaceID = 489109 or itt.InterfaceID = 1220996))
group by itt.InterfaceID
order by itt.InterfaceID asc;

I keep getting error:

Column 'InterfaceTraffic_detail.Out_Maxbps' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 79

Answers (2)

Christopher Gibson
Christopher Gibson

Reputation: 68

Simplifying the query works no problem. There was quite a few small errors that probably came from typing it up rather than the query.

You will be unable to group anything while your including the datetime field.

select 
     max(Out_Maxbps),
     avg(Out_Maxbps),
     max(In_Maxbps),
     avg(In_Maxbps),
     InterfaceID
from InterfaceTraffic_detail 
group by InterfaceID

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

You could wrap it in a CTE and use ROW_NUMBER to take only one row per group:

WITH CTE AS
(
    select 
         MaxOutBPS = max(Out_Maxbps) over (partition by itt.InterfaceID),
         AvgOutBPS = avg(Out_Maxbps) over (partition by itt.InterfaceID),
         MaxInBPS =  max(In_Maxbps) over (partition by itt.InterfaceID),
         AvgInBPS = avg(In_Maxbps) over (partition by itt.InterfaceID),
         itt.InterfaceID, 
         itt.datetime,
         RN = ROW_NUMBER() OVER (PARTITION BY itt.InterfaceID ORDER BY itt.InterfaceID asc)
    from InterfaceTraffic_detail as itt with (nolock)
    where (itt.DateTime >= '2015-06-23 01:00:00'
         and itt.DateTime <= '2015-06-23 05:00:00'
         and (itt.InterfaceID = 489108 or itt.InterfaceID = 489109 or itt.InterfaceID = 1220996))
)
SELECT * FROM CTE
WHERE RN = 1
order by InterfaceID asc;

Upvotes: 2

Related Questions