Re: Nested WHERE

[Jopoy Solano]

Awesome! Thank you very much Claudio!  :)


On Thu, Nov 21, 2013 at 10:40 AM, Claudio Nanni claudio.na...@gmail.comwrote:

 Hi Jopoy,

 Try this:

 SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
 EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM
 CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) 
 EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
 HAVING  total_usage  322100
 ORDER BY total_usage DESC;


 On values derived from group functions you have to use HAVING instead of
 WHERE,
 WHERE filters the records before the grouping, HAVING once grouping is
 done.

 Cheers

 Claudio


 2013/11/21 Jopoy Solano m...@jopoy.com

 Hi! I'm not sure how to phrase this question... anyway, here it is:

 I'm trying to show users in DB radius who have exceeded 322100 bytes
 (3GB) within the current month. As of writing I can only display total
 usage by user with this:

 SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
 EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM
 CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) 
 EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
 ORDER BY total_usage DESC;

 I wanted to add something like a WHERE total_usage  322100 line but
 I don't know where to insert it. Any help would be greatly appreciated.

 Jopoy




 --
 Claudio

Re: Nested WHERE

[Claudio Nanni]

Hi Jopoy,

Try this:

SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM
CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) 
EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
HAVING  total_usage  322100
ORDER BY total_usage DESC;


On values derived from group functions you have to use HAVING instead of
WHERE,
WHERE filters the records before the grouping, HAVING once grouping is done.

Cheers

Claudio


2013/11/21 Jopoy Solano m...@jopoy.com

 Hi! I'm not sure how to phrase this question... anyway, here it is:

 I'm trying to show users in DB radius who have exceeded 322100 bytes
 (3GB) within the current month. As of writing I can only display total
 usage by user with this:

 SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
 EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM
 CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) 
 EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
 ORDER BY total_usage DESC;

 I wanted to add something like a WHERE total_usage  322100 line but
 I don't know where to insert it. Any help would be greatly appreciated.

 Jopoy




-- 
Claudio

Nested WHERE

[Jopoy Solano]

Hi! I'm not sure how to phrase this question... anyway, here it is:

I'm trying to show users in DB radius who have exceeded 322100 bytes
(3GB) within the current month. As of writing I can only display total
usage by user with this:

SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE
EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM
CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) 
EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username
ORDER BY total_usage DESC;

I wanted to add something like a WHERE total_usage  322100 line but
I don't know where to insert it. Any help would be greatly appreciated.

Jopoy