Clever Geek Handbook

Django - Get sum of a field where 2 more fields are equal

I have a table with fields bytes_in

, mac_src

and mac_dst

. I want to get the field sum of bytes_in

all rows where fields mac_src

and mac_dst

are equal, and then sort that sum from highest to lowest. The returned Queryset must have only one record for the mac_src

and values mac_dst

.

In other words, I need to summarize the field bytes_in

, as if mac_src

, and mac_dst

were the same field with the same value.

Thank.

+-------------------+-------------------+----------+
| mac_src           | mac_dst           | bytes_in |
+-------------------+-------------------+----------+
| aa:aa:aa:aa:aa:aa | bb:bb:bb:bb:bb:bb |       10 |
| bb:bb:bb:bb:bb:bb | aa:aa:aa:aa:aa:aa |       20 |
| cc:cc:cc:cc:cc:cc | aa:aa:aa:aa:aa:aa |       30 |
+-------------------+-------------------+----------+
+3


source to share


3 answers 


select mac_src,sum(case when mac_src=mac_dst then bytes_in else null end)
from table
group by mac_src
order by sum(case when mac_src=mac_dst then bytes_in else null end) desc;


0


source 


from django.db.models import F, Sum

items = (
    Item.objects
    .filter(mac_src=F('mac_dst'))
    .values('mac_src', 'mac_dst')
    .annotate(sum_bytes=Sum('bytes_in'))
    .order_by('-sum_bytes')
)


0


source 


select
  t1.mac_src as mac,
  t1.sum_bytes_in + t2.sum_bytes_in as sum_bytes_in
from
  (select
    mac_src,
    sum(bytes_in) as sum_bytes_in
  from tbl
  group by mac_src) as t1
  inner join
  (select
    mac_dst,
    sum(bytes_in) as sum_bytes_in
  from tbl
  group by mac_dst) as t2
  on t1.mac_src = t2.mac_dst
order by sum_bytes_in desc

This assumes row.mac_src! = Row.mac_dst for any row.

0


source






More articles:


All Articles