Hai ...
Saya pengen menghitung berapa jumlah AccountID yg jam pakainya udah abis (
TotTimeLeft <= 0 ) untuk masing-masig PlanID ( plan1 ada berapa AccountID
?, plan2 ada berapa AccountID ? )
Berikut query ke table mysql (ic-radius mysql table) yg udah saya lakukan
dan berhasil utk me-list AccountID yg jam pakainya (TotTimeLeft ) udah habis
:
mysql> select usergroup.UserName as AccountID,
-> usergroup.GroupName as PlanID,
-> radgroupcheck.value as MaxTimeLimit,
-> SUM(radacct.AcctSessionTime) as TotTimeUsage,
-> ( radgroupcheck.value - SUM(radacct.AcctSessionTime) ) as TotTimeLeft
-> from radacct,usergroup,radgroupcheck
-> where (radacct.username = usergroup.username)
-> and (usergroup.groupname = radgroupcheck.groupname)
-> and (usergroup.username = radacct.Username)
-> group by usergroup.username
-> having TotTimeLeft <= 0;
+-----------+--------+--------------+--------------+-------------+
| AccountID | PlanID | MaxTimeLimit | TotTimeUsage | TotTimeLeft |
+-----------+--------+--------------+--------------+-------------+
| vtptpr | plan1 | 600 | 600 | 0 |
| wbgqxc11 | plan2 | 900 | 903 | -3 |
+-----------+--------+--------------+--------------+-------------+
2 rows in set (0.00 sec)
saya mau coba pakai count() tapi bingung syntaxnya ...
hasil yg saya perlukan untuk bisa mengetahui :
- plan1 ada berapa AccountID yg TotTimeLeft <=0 ?
- plan2 ada berapa AccountID yg TotTimeLeft <=0 ?
caranya gimana yah ? pls help.
Thanks
salam,
-rianu-
--
Berhenti langganan: [EMAIL PROTECTED]
Arsip dan info: http://linux.or.id/milis.php
