SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
vinay.varaala
vinay.varaala
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 117
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)
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 860
you don't need the group by clause... @readingdate is a variable, not a cloumn

MVDBA
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9194 Visits: 8492
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

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


Craig Wilkinson - Software Engineer
LinkedIn
vinay.varaala
vinay.varaala
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 117
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..
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 860
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
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 860
and add some function to limit the records - such as

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

MVDBA
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 860
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
vinay.varaala
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 117
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
vinay.varaala
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 117
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
-
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 860
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search