I have two tables: one that is called City (City_id,Name, Latitude, Longitude) and the other is Measures (City_id, Measure_Date, Temperature, Humidity, WindSpeed, AirPressure).
City_id is the primary key in City and foreign key in Measures. City_id and Measure_date are primary key too.
I am trying to write a stored procedure in SQL Server 2005 that allows me to calculate the averages of the all four measurements I have in the database for a specific city. And if the city has no records in Measures, I want the default value to be null.
How should I make it set the default value null for those cities? Thank you :)
Here is the code that I did:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[low_temp]
@temp real
AS
begin
select
avg(m.temperature), avg(m.humidity),
avg(m.windspeed), avg(m.airpressure)
from
city c, measures m
where
m.city_id = @temp
end
Aucun commentaire:
Enregistrer un commentaire