May 25, 2015 at 4:45 am
Hi Everyone,
i would like the user to select a month of the year and then get the average of the columns in that month.
i know how to select the month with
select * from Petrol_Table where FuelDate like '2014-06%';
don't know if this is the correct way to do it, or how to proceed from here...
Many Thanks for reading.
May 25, 2015 at 4:46 am
Without knowing what columns are in the table, all I can suggest is the AVG function, maybe with a GROUP BY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 4:58 am
opps, 😉
the three columns contain ie... 138.1, 44.18, 66.49
have used the "AVG" before...
but have no idea how to use Groupby....
if you have the time, some code would help....
Thanks
May 25, 2015 at 5:04 am
sherm (5/25/2015)
if you have the time, some code would help....
Err, how am I going to write working code for you when you haven't told me the names of the columns in the table....?
SELECT AVG(SomeColumn), AVG(SomeOtherColumn), AVG(YetAnotherColumn)
FROM Petrol_Table where FuelDate >= '2014-06-01' AND FuelDate < '2014-07-01'
Which probably doesn't do anything close to what you want.
Please remember I can't see your screen, I have no idea what your tables look like and I don't know what you're trying to do beyond the brief description in your first post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 5:18 am
hmmm, lets try that again... sorry about this...
column names are total_liters, price_a_liter, total_dollars
i have tried your select line and SQL do not like it heheeh(my mistake) just tried it again and it works, my fingers get in the way sometimes...heheeeh
but when i tired this...
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter) from Petrol_Table where FuelDate like '2014-06%'
it gave me this result col1 = 38.715, col2=55.8075, col3=143.7
which is correct, thanks very much for the lesson.:-D
May 25, 2015 at 5:34 am
Have also done this for a weekly Average....
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter)
from Petrol_Table
where FuelDate >= '2014-06-01' and fueldate <= '2014-06-07';
Many Thanks for you help.:cool:
May 25, 2015 at 5:53 am
sherm (5/25/2015)
but when i tired this...
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter) from Petrol_Table where FuelDate like '2014-06%'it gave me this result col1 = 38.715, col2=55.8075, col3=143.7
which is correct, thanks very much for the lesson.:-D
Don't do it that way. String manipulation on datetime data types is not a good idea. When querying ranges of datetime data types you should use >= and < with the two ends of the range.
What was wrong with the code I posted? that is, what does the following (corrected for the column names) do wrong?
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter) from Petrol_Table
FuelDate >= '2014-06-01' AND FuelDate < '2014-07-01'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 6:02 am
There was nothing wrong, just that my fingers got in the way, and i did not notice that i had typed your line incorrectly.
if i just want the month, how should i code it.?
May 25, 2015 at 6:05 am
sherm (5/25/2015)
if i just want the month, how should i code it.?
That's what the query I posted does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 6:09 am
sherm (5/25/2015)
Have also done this for a weekly Average....
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter)from Petrol_Table
where FuelDate >= '2014-06-01' and fueldate <= '2014-06-07';
btw, that's wrong for a weekly average. That's going to get you 6 days, not 7.
It will get you:
2014-06-01, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
2014-06-02, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
2014-06-03, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
2014-06-04, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
2014-06-05, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
2014-06-06, from 00:00:00 (midnight) to 23:59:59 (just before midnight)
It will not get you any rows from 2014-06-07 unless the time portion of the datetime column exactly midnight (00:00:00). If fuel was purchased at 2014-06-07 07:12:56, it will not be returned by your query.
For the week from 2014-06-01 to 2014-06-07 (inclusive), you need:
select AVG(total_liters), AVG(total_dollars), AVG(price_a_liter) from Petrol_Table
where FuelDate >= '2014-06-01' and fueldate < '2014-06-08';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 6:16 am
OK, i found out the problem....hehee
i was using the dates 2014-06-01 - 2014-06-31... hehehe
and of course the SQL was Smarter than me.;-) it knows that June only has 30 days NOT 31.:-D
and as to the week will have to adjust it....
May 25, 2015 at 6:23 am
OK, found out what the problem was...;-)
SQL is smarter than me.... I was using the dates 2014-06-01 - 2014-06-31 SQL knows that June has 30 NOT 31:-D
As to the week I will adjust it.
May 25, 2015 at 6:33 am
sherm (5/25/2015)
and of course the SQL was Smarter than me.;-) it knows that June only has 30 days NOT 31.:-D
🙂
When doing date filters like this, always do a >= on the first day you want included in the result and < (not <=) on one day after the last day you want included in the result. This allows for times to be accounted for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 6:38 am
GilaMonster (5/25/2015)
sherm (5/25/2015)
and of course the SQL was Smarter than me.;-) it knows that June only has 30 days NOT 31.:-D🙂
When doing date filters like this, always do a >= on the first day you want included in the result and < (not <=) on one day after the last day you want included in the result. This allows for times to be accounted for.
The Grasshopper bows to your Greater Knowledge .:-)
Many Thanks
Rdgs.
Michael
May 25, 2015 at 6:52 am
You're welcome
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply