On Mon, Nov 12, 2001 at 11:14:21AM -0500, Johnson, Gregert wrote: % SELECT SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) % FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName % WHERE d.UserName = 'foo'; % % Or, to summarize for all users: % % SELECT d.UserName as user, SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) % FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName % GROUP BY user;
D'oh, I forgot something in my post. monthly_usage actually contains one row per month per user. I need to add the SUM() from detail to the monthly_usage row for the *current month*. This query: SELECT SUM(detail.AcctSessionTime) + IFNULL(monthly_usage.Minutes, 0) FROM detail LEFT OUTER JOIN monthly_usage ON detail.UserName = monthly_usage.UserName WHERE detail.UserName = 'johnmorr' AND monthly_usage.UserName = 'johnmorr' AND monthly_usage.UsageDate = '2001-11-01'; fails because the user may not have a row in monthly_usage for the current month, so I'm not really sure that I can JOIN the two tables (the WHERE clause won't match if the row doesn't exist). 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
