I'm logging RADIUS detail records to a MySQL database. Currently, I crunch the the detail table (containing individual records) once a month into another table that contains aggregate usage (monthly_usage).
CREATE TABLE monthly_usage ( UserName varchar(32) NOT NULL, Realm varchar(64) NOT NULL, UsageDate date NOT NULL, Minutes mediumint unsigned, Logins mediumint unsigned, PRIMARY KEY (Username, Realm, UsageDate) ); The problem is, the detail table is getting to be very large toward the end of the month. I'd like to crunch statistics weekly (or even nightly) to keep disk usage at a reasonable level. I've thought of doing something like: SELECT SUM(detail.AcctSessionTime) + monthly_usage.Minutes FROM detail,monthly_usage WHERE detail.UserName = 'foo' AND monthly_usage.UserName = 'foo'; but this won't work because a user might not always have a row in the monthly_usage table (they might not have logged in yet this month), which makes this query return NULL. My question is this: is there a way to add SUM(detail.AcctSessionTime) to a corresponding row from montly_usage, even if that row from monthly_usage doesn't exist (e.g., add 0 to SUM() if the monthly_usage row doesn't exist). I've considered breaking this up into two queries; one to select all usernames from the detail table and insert "dummy" rows into monthly_usage with 0 usage, then run the query above, which will then work as expected. This seems kind of kludgy; is there a way to do what I want in a single query? thanks, john -- John Morrissey _o /\ ---- __o [EMAIL PROTECTED] _-< \_ / \ ---- < \, www.horde.net/ __(_)/_(_)________/ \_______(_) /_(_)__ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
