SQL Query MySql Group By

November 4, 2022

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?

>Solution :

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.