Each GROUP BY expression must contain at least one column that is not an outer reference.

  • below is the code:

    create proc sp_hrdate

    @red datetime

    as

    declare @Reading_Date datetime

    set @Reading_Date=@red

    select DatePart(hour,@Reading_Date) as hour, convert(Date,@Reading_Date,103) as date, avg(Fuel_Level)as fuel_level,avg(Fuel_Volume)as fuel_volume from Levels

    where Simmons_PanelID=5479

    group by DatePart(hour,@Reading_Date),convert(Date,@Reading_Date,103)

    order by DatePart(hour,@Reading_Date)

  • you don't need the group by clause... @readingdate is a variable, not a cloumn

    MVDBA

  • I guess that this is what you're after.

    CREATE PROCEDURE sp_hrdate @red DATETIME AS

    BEGIN

    DECLARE @Reading_Date DATETIME;

    SET @Reading_Date = @red;

    SELECT DATEPART(HOUR,@Reading_Date) AS [hour], CONVERT(DATE,@Reading_Date,103) AS [date],

    fuel_level, fuel_volume

    FROM (SELECT AVG(Fuel_Level), AVG(Fuel_Volume)

    FROM Levels

    WHERE Simmons_PanelID = 5479

    )a(fuel_level, fuel_volume);

    END

    You've not really posted in a good forum for asking a SQL question by the way 😉


    --EDIT--

    Ah, I was late. So maybe it is the right forum to ask questions?! :hehe:


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi thanks,

    when I executed with modify query i got this as output

    hour date fuel-LEVEL FUEL_VOLUME

    102012-05-081195.96343612858.097037

    MY QUESTION IS IF I GIVE ONE PARTICULAR DAY IT SHOULD DISPLAY ALL THE DAY RECORDS BY SUMMING UP TO HOUR SO IT SHOULD DISPLAY LIKE HOUR

    0

    1

    2 AND RESPECTIVELY COLUMSN..

    CHEERS..

  • what is the date column on the levels table called then ? you didn't post the table structure so we can't really help

    but you need to change @reading date to be the date column in levels table

    MVDBA

  • and add some function to limit the records - such as

    where convert(date,mydatecolumn)=convert(date,@red)

    MVDBA

  • here is an example

    select CONVERT(varchar(20),dateprocessed),COUNT(*) from mydb.dbo.mytable

    where convert(date, dateprocessed)=convert(date,getdate())

    group by CONVERT(varchar(20),dateprocessed)

    order by CONVERT(datetime,CONVERT(varchar(20),dateprocessed)) desc

    MVDBA

  • column name in levels table is same Reading_Date..

    tried with your give function where class. same output only it showing that day oth hour i need rest of the hours till end of day..

    help me plss

  • by using your given example I tried

    select convert(varchar(20), Reading_Date), sum(Fuel_Level) as fuel from dbo.Levels

    where convert(date,Reading_Date)=convert(date,getdate())

    group by Convert (varchar(20),Reading_Date)

    order by convert(datetime, convert(varchar(20),Reading_Date))

    it displaying like this :

    Sep 6 2012 12:00AM179152.80

    Sep 6 2012 12:15AM179509.60

    Sep 6 2012 12:30AM180141.50

    Sep 6 2012 12:45AM180243.30

    Sep 6 2012 1:00AM180227.70

    Sep 6 2012 1:15AM180196.60

    Sep 6 2012 1:30AM180175.20

    I want in output to sum up fuel_level from 6 2012 12:00AM to 6 2012 12:45AM and dispaly date as sep 6 2012

    -- expected output

    date hour fuel_level

    2012-09-06 0 50

    2012-09-06 1 500

    - - -

    - - -

    2012-09-06 24 2530

    cheers

    -

  • ok - so change your grouping and, order by and select to use

    DATEPART(hh,mydatecolumn)

    i'm sure you can then figure out how to display the date in the query

    MVDBA

  • ALTER PROCEDURE sp_hrdate1 @red DATETIME AS

    BEGIN

    DECLARE @Reading_Date DATETIME;

    SET @Reading_Date = @red;

    SELECT DATEPART(HOUR,Reading_Date) AS [hour], convert(varchar(20), Reading_Date) AS [date],

    fuel_level, fuel_volume

    FROM (SELECT AVG(Fuel_Level), AVG(Fuel_Volume), Reading_Date

    FROM Levels

    WHERE Simmons_PanelID = 5479 and convert(date,Reading_Date)=convert(date,@Reading_Date)

    group by DATEPART(HOUR,Reading_Date),Reading_Date

    )Levels(fuel_level, fuel_volume, Reading_Date)

    END

    output:

    0Sep 5 2012 12:00AM1082.30000010560.464000

    0Sep 5 2012 12:15AM1081.84000010555.460000

    0Sep 5 2012 12:30AM1081.84000010555.460000

    0Sep 5 2012 12:45AM1081.68000010552.906000

    ------------------------------------------------------------------------------------

    expected output :

    :--------------------------------------------------------------------------------

    0 Sep 5 2012 1587080 462466

    sumup the minues to hour is it possible pls

  • ok - you've switched back from sum to average now - so i've modified to do both

    think about what you are grouping by ---- you've still got convert(varchar(20)....) in there - this will get you down to minute level grouping

    i've re-written it for you and made it pretty

    ALTER PROCEDURE sp_hrdate1 @red DATETIME AS

    BEGIN

    DECLARE @Reading_Date DATETIME;

    SET @Reading_Date = @red;

    SELECT dateadd(hh,convert(datetime,convert(date,@red)),DATEPART(HOUR,Reading_Date)) AS hour, convert(varchar(20), Reading_Date) AS [date],AVG(Fuel_Level), AVG(Fuel_Volume),SUM(fuel_level),SUM(fueld_volume)

    FROM Levels

    WHERE Simmons_PanelID = 5479 and convert(date,Reading_Date)=convert(date,@Reading_Date)

    group by dateadd(hh,convert(datetime,convert(date,@red)),DATEPART(HOUR,Reading_Date))

    END

    MVDBA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply