Hi, I created a view v_weather_data of a data which is supposed to be used by several SELECTs joined together. Separate SELETs using the view work fine, but when I join them together I am receiving following error: SQL Error [1064] [42000]: (conn=36) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ’
. Not really sure what is wrong with this query and why the syntax is not righ near " ’ " . Any ideas would be much appreciated. I work using MariaDB.
SELECT
temp_avg.*,
CASE WHEN rain_count.rainy_hours IS NULL THEN 0 ELSE rain_count.rainy_hours,
gust_max.max_wind_gust_km_h
FROM
(SELECT
date, city, country, AVG(daily_avg_temp_°c) AS 'daily_avg_temp_°c'
FROM v_weather_data
WHERE time IN ('06:00', '09:00', '12:00', '15:00', '18:00')
GROUP BY date, city, country
ORDER BY date DESC, city ASC) temp_avg
LEFT JOIN
(SELECT
DISTINCT date,
city,
country,
Count(rainy_hours) AS 'rainy_hours'
FROM v_weather_data
WHERE rainy_hours>0
GROUP BY date, city, country) rain_count
ON temp_avg.country = rain_count.country
AND temp_avg.date = rain_count.date
LEFT JOIN
(SELECT
date,
city,
country,
Max(max_wind_gust_km_h) AS 'max_wind_gust_km_h'
FROM v_weather_data
GROUP BY date, city, country
ORDER BY date DESC, city ASC) gust_max
ON temp_avg.country = gust_max_country
AND temp_avg.date = gust_max.date
You should be able to simplify that greatly so you’re only doing one table scan.
SELECT date
, city
, country
, AVG(CASE WHEN time IN ('06:00', '09:00', '12:00', '15:00', '18:00') THEN daily_avg_temp_°c
ELSE NULL END) AS avg_temp
, SUM(CASE WHEN rainy_hours > 0 THEN 1 ELSE 0 END) AS rainy_hours
, MAX(max_wind_gust_km_h) AS max_wind_gust
FROM v_weather_data
GROUP BY date
, city
, country
ORDER BY date DESC
, city ASC
there’s at least one syntax error, in the second last line, where gust_max_country should be gust_max.country
ORDER BY clauses in a derived table subquery are ignored
DISTINCT is redundant with GROUP BY
the biggest red flag to me is that your subqueries, all three of them, produce granular data at the level of date, city, country and yet your ON clauses in the joins only join on two of these three columns – this is guaranteed to produce messed up output results
try this –
SELECT temp_avg.*
, COALESCE(rain_count.rainy_hours,0) AS rainy_hours
, gust_max.max_wind_gust_km_h
FROM ( SELECT date
, city
, country
, AVG(daily_avg_temp_°c) AS 'daily_avg_temp_°c'
FROM v_weather_data
WHERE time IN ('06:00', '09:00', '12:00', '15:00', '18:00')
GROUP
BY date
, city
, country ) AS temp_avg
LEFT
JOIN ( SELECT date
, city
, country
, COUNT(rainy_hours) AS 'rainy_hours'
FROM v_weather_data
WHERE rainy_hours > 0
GROUP
BY date
, city
, country ) AS rain_count
ON rain_count.country = temp_avg.country
AND rain_count.city = temp_avg.city
AND rain_count.date = temp_avg.date
LEFT
JOIN ( SELECT date
, city
, country
, MAX(max_wind_gust_km_h) AS 'max_wind_gust_km_h'
FROM v_weather_data
GROUP
BY date
, city
, country ) AS gust_max
ON gust_max.country = temp_avg.country
AND gust_max.city = temp_avg.city
AND gust_max.date = temp_avg.date
Guys, thanks so much to all of you for the valuable insight and detailed recommendations! It allowed me to understand, why the way I approach this queries was not always right or the best. Indeed, what @DaveMaxwell and @r937 suggested works perfectly! And @DaveMaxwell, your suggestion was really a huge simplification but functional!
