Question 112866 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Query MySql Group By

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?

enter image description here

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    We can use this query which uses sign() function (if traffic_in > traffic_out, then traffic_in-traffic_out = positive value and sign returns +1 and order by 1 to display all in ascending of protocol

    select protocol, traffic_in, traffic_out from traffic where sign(traffic_in-traffic_out)=1 order by 1;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search