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

Question

Post reply

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

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 6, 2012 at 3:59 am

#74327

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)

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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

MVDBA

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

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/

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 3

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..

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

and add some function to limit the records - such as

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

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

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

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 7

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

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 8

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

-

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

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

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 10

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

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