--Le 11/05/2003 09:48 -0700, Chris Linstruth écrivait :
|
| But this can take 15-30 seconds:
|
| SELECT count(radacctid) AS sessions,
| sum(acctsessiontime) AS connecttime
| FROM radacct
| WHERE radacctid IN
| (SELECT DISTINCT ON (acctsessionid) radacctid FROM radacct
| WHERE username='cjl'
| AND acctstoptime IS NOT NULL
| AND date_trunc('month', now())=date_trunc('month',
| acctstoptime));
|
| There are probably many different ways to perform this query. My
| main problem is trying to overcome the fact that try as I might,
| I can't stop the occasional duplicate accounting record from being
| inserted so I have to weed them out, hence the "DISTINCT ON
| (acctsessionid)".
try this :
SELECT count(radacctid) AS sessions, sum(acctsessiontime) AS connecttime FROM radacct JOIN
(SELECTDISTINCT ON (acctsessionid) radacctid FROM radacct WHERE username='cjl' AND
acctstoptimeIS NOT NULL AND date_trunc('month', now())=date_trunc('month',
acctstoptime)) AS subselect USING (radacctid);
--
Mathieu Arnold