SELECT and GROUP BY

  • 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,

  • Hi ,

    Can you post some sample data for Us??

    Thanks,

    Chandru.

  • 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

  • Hi

    Just change your Group By statement like:

    GROUP BY Tidspunkt,DATEADD(hour,0,DATEADD(hour, DATEDIFF(hour, 0, Tidspunkt), 0))

  • 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

  • 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:):)

  • 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