List:General Discussion« Previous MessageNext Message »
From:Jopoy Solano Date:November 21 2013 11:33pm
Subject:Re: Nested WHERE
View as plain text  
Awesome! Thank you very much Claudio!  :)


On Thu, Nov 21, 2013 at 10:40 AM, Claudio Nanni <claudio.nanni@stripped>wrote:

> 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 > 3221000000
> 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 <mail@stripped>
>
>> 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 3221000000 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 > 3221000000" line but
>> I don't know where to insert it. Any help would be greatly appreciated.
>>
>> Jopoy
>>
>
>
>
> --
> Claudio
>

Thread
Nested WHEREJopoy Solano21 Nov
  • Re: Nested WHEREClaudio Nanni21 Nov
    • Re: Nested WHEREJopoy Solano21 Nov