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;
--Greg Johnson
-----Original Message-----
From: John Morrissey [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 10:45 AM
To: [EMAIL PROTECTED]
Subject: Help with aggregate query
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
---------------------------------------------------------------------
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
