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 |
+-------------------+-------------------+----------+
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;
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')
)
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.