SQL Query MySql Group By-CodePudding

I have a table called traffic shown below. I have to write a query which list all the protocols for which incoming traffic is higher than outgoing. The result should be in this format - protocol, traffic_in, traffic_out and sorted in ascending by protocol.

Query I wrote was,

select 
    protocol
    , sum(traffic_in)
    , sum(traffic_out) 
from traffic 
group by protocol 
order by protocol asc;

but it did not work. I know I am missing something but couldn't figure out. Could someone please help with the query?

CodePudding user response：

You are on the right track with the aggregation query. You did sum the total traffic in both directions for each protocl; all that is left to do is compare those aggregated values.

For this, you would use a HAVING clause ; that's a special WHERE clause, but unlike the latter it is allowed to operate on aggregate functions. So:

select 
    protocol
    , sum(traffic_in)
    , sum(traffic_out) 
from traffic 
group by protocol
having sum(traffic_in) > sum(traffic_out) -- incoming traffic is higher than outgoing
order by protocol;

Side note: asc is the default sort order.

CodePudding user response：

If you want to know if the the comlete sum is higheer you can use HAVING

select 
    protocol
    , sum(traffic_in) as sum_in
    , sum(traffic_out) as sum_ot
from traffic 
group by protocol 
HAVING sum_in > sum_out
order by protocol asc;