CODEWITHSUNDEEP
sqlmysqlgroup-by
benjisail
benjisail

Reputation: 1666

SELECT with MAX and SUM from multiple tables

I have 3 tables :

I would like to get a list of DAILY value from these 3 tables using this select :

SELECT daily_date, daily_value, SUM(rain), MAX(calc_value)

The SUM and the MAX need to be done for all the hour of the day.

This is what I did :

SELECT
date_format(convert_tz(daily_date, 'GMT', 'America/Los_Angeles'), '%Y-%m-%d 00:00:00') as daily_date_gmt,
daily_value,
SUM(rain),
MAX(calc_value)
FROM weather_data_daily wdd, weather_data wd, weather_data_calculated wdc
WHERE daily_date_gmt=date_format(convert_tz(wd.hourly_date, 'GMT', 'America/Los_Angeles'), '%Y-%m-%d 00:00:00')
and daily_date_gmt=date_format(convert_tz(wdc.hourly_date, 'GMT', 'America/Los_Angeles'), '%Y-%m-%d 00:00:00')
group by daily_date_gmt
order by daily_date_gmt;

This didn't work because I don't know how to deal with the group by in this case.

I also try to use a temporary table but without success too.

Thanks for your help!

Upvotes: 0

Views: 981

Answers (3)

pipTheGeek
pipTheGeek

Reputation: 2713

Either include daily_value in your group by, or use two queries. One will contain the date column and the two aggregates, the other will contain the date column and daily value. you can then use a single outer query to join these result sets on the date column.

EDIT: You say in your comment that including daily_value in the group by means the query doesn't complete. This is because (probably) you have no join criteria between all the tables your query includes. This will result in a potentially VERY large result set which would take a very long time. I don't mind helping with the actual SQL but you will need to update your question so that we can see which fields are coming from which tables.

Upvotes: 1

jle
jle

Reputation: 9489

try this:

select a.daily_date, a.daily_value, SUM(b.rain), MAX(c.calc_value)
from weather_data_daily a,weather_data b,weather_data_calculated c
where convert(varchar, a.daily_date, 101)=convert(varchar, b.hourly_date, 101)
and convert(varchar, a.daily_date, 101)=convert(varchar, c.hourly_date, 101)
group by a.daily_date, a.daily_value

You have to connect the tables together somehow (this uses an inner join). This requires getting the hourly dates and other dates in the same format. This gives them the format MM/DD/YYYY.

Upvotes: 0

user521565
user521565

Reputation: 61

Assuming you only have one entry for daily_date, daily_value in 'weather_data_daily' you should GROUP BY daily_date, daily_value, then your aggregrations (SUM and MAX) will operate on the correct grouping.

Upvotes: 0

Related Questions