JA,
To have a SELECT statement generate a row for every day in the year, either your raindata table needs a row for every day in the year, or you need another table which has a row for every day of the year. Supposing you have such a table, call it 'calendar' with a date column named 'yearday', then you could retrieve daily rainfall including NULLs with
SELECT calendar.yearday, rainfall.amount FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate ORDER BY calendar.yearday;
or if there can be multiple raindata rows for a date then
SELECT calendar.yearday, SUM( rainfall.amount ) FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate GROUP BY calendar.yearday;
but your rainfall column ought to be numeric.
Peter Brawley http://www.artfulsoftware.com
-----
[EMAIL PROTECTED] wrote:
I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10)
What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is:
select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata
order by dayPart, monthPart
just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much.
--ja
-- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
