SQL- max, min and avg and date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL- max, min and avg and date

Author

Topic

pappandin
Starting Member

9 Posts

Posted - 2011-11-06 : 18:08:20

I'm currently working on a weather forcast project. I have a "report" page, where you can report the weather for each day, week and month. I want to get the MAX(), MIN() and the AVG() data of the temperature, wind speed and so forth, but aswel as the max and min date (the date they were observed).

I fiddled around in sql and came up with this:

My DB looks like this:

observations
- observationid (int)
- date (DATE)
- temperature (float)
- Wind speed
- and some tables which arent necessary for you to know.

I got some help from here a couple a weeks ago, but the sql-code had some flaws:

SELECT t.date,t.temperature
FROM observations t
INNER JOIN (SELECT MIN(temperature) AS val
FROM observations
UNION ALL
SELECT MAX(temperature) AS val
FROM observations)t1
On t1.Val = t.temperature

It shows the date of the max and min data aswel as the data, but if I add two observations which both has -20 celsius, it will only show those and not max and min.

All input would be appriciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-06 : 23:17:36

quote:
I got some help from here a couple a weeks ago

Strange, if you have posted here before, your post count should not be 1. Can you show us the link to that thread ?

quote:
I'm currently working on a weather forcast project. I have a "report" page, where you can report the weather for each day, week and month. I want to get the MAX(), MIN() and the AVG() data of the temperature, wind speed and so forth, but aswel as the max and min date (the date they were observed).

based on these, this is probably what you want, for daily


select date, min(temperature), max(temperature), avg(temperature)
from   observations
group by date

for weekly, change the date to dateadd(week, datediff(week, 0, date), 0)
for monthly change to dateadd(month, datediff(month, 0, date), 0)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:12:20

quote:
Originally posted by khtan

quote:
I got some help from here a couple a weeks ago

Strange, if you have posted here before, your post count should not be 1. Can you show us the link to that thread ?

May be OP was using another username then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:13:38

and this was the thread. Apparently I had given asked solution then

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167488

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:16:34

quote:
Originally posted by pappandin

I'm currently working on a weather forcast project. I have a "report" page, where you can report the weather for each day, week and month. I want to get the MAX(), MIN() and the AVG() data of the temperature, wind speed and so forth, but aswel as the max and min date (the date they were observed).

I fiddled around in sql and came up with this:

My DB looks like this:

observations
- observationid (int)
- date (DATE)
- temperature (float)
- Wind speed
- and some tables which arent necessary for you to know.

I got some help from here a couple a weeks ago, but the sql-code had some flaws:

SELECT t.date,t.temperature
FROM observations t
INNER JOIN (SELECT MIN(temperature) AS val
FROM observations
UNION ALL
SELECT MAX(temperature) AS val
FROM observations)t1
On t1.Val = t.temperature

It shows the date of the max and min data aswel as the data, but if I add two observations which both has -20 celsius, it will only show those and not max and min.

All input would be appriciated!

If bth readings are -20 then what should be output? you want max and min date when it happened?


SELECT MAX(t.date) AS maxDate,MIN(t.date) AS MinDate,t.temperature
FROM observations t
INNER JOIN (SELECT MIN(temperature) AS val
            FROM observations
            UNION ALL
            SELECT MAX(temperature) AS val
            FROM observations)t1
On t1.Val = t.temperature
GROUP BY t.temperature

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pappandin
Starting Member

9 Posts

Posted - 2011-11-07 : 05:09:12

My friend posted the other topic; he is on holiday right now, so I had to make a new one.

If i have inserted some observations and lets say for this case that in a week i got the temperatures 1, 1, 3, 4, 5 ,6 and 7.

If were to use this:

SELECT MAX(t.date) AS maxDate,MIN(t.date) AS MinDate,t.temperature
FROM observations t
INNER JOIN (SELECT MIN(temperature) AS val
FROM observations
UNION ALL
SELECT MAX(temperature) AS val
FROM observations)t1
On t1.Val = t.temperature
GROUP BY t.temperature

it will show me 1 and 1 and the dates of those two. It works when there is no matching values, if you understand.

The out put should be like

12. 03.11 1
12.10.11 7

I'm pretty new to this and I might be asking some silly questions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:08:03

then this should solve it right?


SELECT MIN(t.date) AS date,t.temperature
FROM observations t
INNER JOIN (SELECT MIN(temperature) AS val
FROM observations
UNION ALL
SELECT MAX(temperature) AS val
FROM observations)t1
On t1.Val = t.temperature
GROUP BY t.temperature

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pappandin
Starting Member

9 Posts

Posted - 2011-11-07 : 07:45:13

It does if I only have to temperatures, but if I have reported -20 two times, it will only show -20 two times :S. I thought it worked, but when I put in other temperatures it only shows the lowest

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-07 : 08:09:11

try


; with observ as
(
     select *, 
            min_rn = row_number() over (order by temperature),
            max_rn = row_number() over (order by temperature desc)
     from   observations
)
select  *
from    observ 
where   min_rn = 1
or      max_rn = 1

KH
[spoiler]Time is always against us[/spoiler]

pappandin
Starting Member

9 Posts

Posted - 2011-11-07 : 08:22:25

I'm sorry, but I got sql errors with that one. The only problem is that if I use visakh16's sql, it only shows the MIN temperatures if you have in you DB -20, -20, 15 and 20; it will only show -20 and -20.

I thought the sql worked, but when i inserted the extra -20 it only put out:

2011-10 - 13 -20
2011-10 - 14 -20

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-07 : 08:36:57

what's the error message ?

KH
[spoiler]Time is always against us[/spoiler]

pappandin
Starting Member

9 Posts

Posted - 2011-11-07 : 08:53:28

Fixed it; human error. Ty for the help guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:14:28

quote:
Originally posted by pappandin

Fixed it; human error. Ty for the help guys

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources