Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Each GROUP BY expression must contain at least one column that is not an outer reference. Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 3:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:37 AM
Points: 32, Visits: 108
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)
Post #1355167
Posted Thursday, September 6, 2012 4:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
you don't need the group by clause... @readingdate is a variable, not a cloumn

MVDBA
Post #1355180
Posted Thursday, September 6, 2012 4:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 2,422, Visits: 7,437
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?!



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1355182
Posted Thursday, September 6, 2012 4:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:37 AM
Points: 32, Visits: 108
Hi thanks,

when I executed with modify query i got this as output

hour date fuel-LEVEL FUEL_VOLUME
1 0 2012-05-08 1195.963436 12858.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..
Post #1355194
Posted Thursday, September 6, 2012 5:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
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
Post #1355200
Posted Thursday, September 6, 2012 5:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
and add some function to limit the records - such as

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


MVDBA
Post #1355203
Posted Thursday, September 6, 2012 5:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
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
Post #1355207
Posted Thursday, September 6, 2012 5:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:37 AM
Points: 32, Visits: 108
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
Post #1355208
Posted Thursday, September 6, 2012 5:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:37 AM
Points: 32, Visits: 108
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:00AM 179152.80
Sep 6 2012 12:15AM 179509.60
Sep 6 2012 12:30AM 180141.50
Sep 6 2012 12:45AM 180243.30
Sep 6 2012 1:00AM 180227.70
Sep 6 2012 1:15AM 180196.60
Sep 6 2012 1:30AM 180175.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
-
Post #1355222
Posted Thursday, September 6, 2012 5:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
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
Post #1355226
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse