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
48703114/03/2008 21:27:5745,72228,611,430-3276,8-1329-13,29283,46-13,292025,4-107,1-12605-132,9NULLNULLNULL
48704114/03/2008 21:33:0230,88154,47,720-3276,8-1329-13,29283,46-13,292025,4-107,1-12605-132,9NULLNULLNULL
48705114/03/2008 23:38:0517,8889,44,470-3276,899999999,99283,46999,992025,4-107,1-126059999,9NULLNULLNULL
48706114/03/2008 23:43:094,9624,81,240-3276,899999999,99283,46999,992025,4-107,1-126059999,9NULLNULLNULL
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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