July 23, 2008 at 4:52 am
is there anyone that can help me widh this Queri.
SELECT TOP 1000 Tidspunkt,
AVG(VannForing), AVG(Vannstand), AVG(Dosering),
AVG(Hastighet), AVG(Silo), AVG(Konduktivitet),
AVG(PumpeTrykk), AVG(phOpp), AVG(phNed),
AVG(VannTemp), AVG(Batteri), AVG(Brensel), AVG(LuftTemp) ,
DATEADD(hour,0,DATEADD(hour, DATEDIFF(hour, 0, Tidspunkt), 0)) as dte
FROM HistorTR
WHERE StationID = 1
AND (Tidspunkt < DATEADD(hour, -24, GETDATE()))
GROUP BY DATEADD(hour,0,DATEADD(hour, DATEDIFF(hour, 0, Tidspunkt), 0))
ORDER BY dte
When I Select Tidspunkt (datetime) in this sql-statment, its give me this error.
Column 'HistorTR.Tidspunkt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
When put Tidspunkt into the GROUP BY, is give me all the data, not the AVG data.
widhout Select Tidspunkt, the queri is OK,
July 23, 2008 at 5:15 am
Hi ,
Can you post some sample data for Us??
Thanks,
Chandru.
July 23, 2008 at 5:29 am
some sample data from HistorTR table
48703 1 14/03/2008 21:27:57 45,72 228,6 11,43 0 -3276,8 -1329 -13,29 283,46 -13,29 2025,4 -107,1 -12605 -132,9 NULL NULL NULL
48704 1 14/03/2008 21:33:02 30,88 154,4 7,72 0 -3276,8 -1329 -13,29 283,46 -13,29 2025,4 -107,1 -12605 -132,9 NULL NULL NULL
48705 1 14/03/2008 23:38:05 17,88 89,4 4,47 0 -3276,8 99999 999,99 283,46 999,99 2025,4 -107,1 -12605 9999,9 NULL NULL NULL
48706 1 14/03/2008 23:43:09 4,96 24,8 1,24 0 -3276,8 99999 999,99 283,46 999,99 2025,4 -107,1 -12605 9999,9 NULL NULL NULL
July 23, 2008 at 5:49 am
Hi
Just change your Group By statement like:
GROUP BY Tidspunkt,DATEADD(hour,0,DATEADD(hour, DATEDIFF(hour, 0, Tidspunkt), 0))
July 23, 2008 at 6:17 am
Can you show the column names too? We need to understand where Tidspunkt is coming from.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 23, 2008 at 7:03 am
widh jus of group by Tidspunkt.. i dont get the AVG data, but all data,
when i try min(Tidspunkt), AVG(Vannforing),AVG.....
then i get the right data.
Thanks for the inputs:):)
July 23, 2008 at 7:08 am
Dim valg As String = "min(Tidspunkt), AVG(VannForing), AVG(Vannstand), AVG(Dosering), AVG(Hastighet), AVG(Silo), " & _
"AVG(Konduktivitet), AVG(PumpeTrykk), " & _
"AVG(phOpp), AVG(phNed), AVG(VannTemp), AVG(Batteri), AVG(Brensel), AVG(LuftTemp) "
sqlStr = "SELECT TOP 1000 " & valg & ", dateadd(hour,0,dateadd(hour, datediff(hour, 0, Tidspunkt), 0)) as dte " & _
"from HistorTR " & _
"WHERE StationID = " + lbl_stasjonsID.Text + " AND (Tidspunkt < DateAdd(hour, -24, getdate())) " & _
"GROUP BY DATEADD(hour, 0, DATEADD(hour, DATEDIFF(hour, 0, Tidspunkt), 0))" & _
"ORDER BY dte"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply