-- use traffic;

select
	out.dstPort as port,
	out.trafOut as trafOut,
	in.trafIn as trafIn
from
	(
		select
			dstPort,
			sum(psize) as trafOut
		from
			traffic
		where (protoId=2) && (srcPort=0) &&
			(srcIp=3232240947) && (dstIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by dstPort
	) as `out` join
	(
		select
			srcPort,
			sum(psize) as trafIn
		from
			traffic
		where (protoId=2) && (dstPort=0) &&
			(dstIp=3232240947) && (srcIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by srcPort
	) as `in` on out.dstPort=in.srcPort

union all

select
	out.dstPort as port,
	out.trafOut as trafOut,
	ifnull(in.trafIn,0) as trafIn
from
	(
		select
			dstPort,
			sum(psize) as trafOut
		from
			traffic
		where (protoId=2) && (srcPort=0) &&
			(srcIp=3232240947) && (dstIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by dstPort
	) as `out` left outer join
	(
		select
			srcPort,
			sum(psize) as trafIn
		from
			traffic
		where (protoId=2) && (dstPort=0) &&
			(dstIp=3232240947) && (srcIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by srcPort
	) as `in` on out.dstPort = in.srcPort
where in.srcPort is null

union all

select
	in.srcPort as port,
	ifnull(out.trafOut,0) as trafOut,
	in.trafIn as trafIn
from
	(
		select
			dstPort,
			sum(psize) as trafOut
		from
			traffic
		where (protoId=2) && (srcPort=0) &&
			(srcIp=3232240947) && (dstIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by dstPort
	) as `out` right outer join
	(
		select
			srcPort,
			sum(psize) as trafIn
		from
			traffic
		where (protoId=2) && (dstPort=0) &&
			(dstIp=3232240947) && (srcIp = 3232241151) &&
			(date >= "2009-05-01" && date < "2009-06-01")
		group by srcPort
	) as `in` on out.dstPort = in.srcPort
where out.dstPort is null;

