MySQL query sum number by row -
i sum row. quick way?
current query:
select t1.name domain, cast(sum(t2.http_out)/1024/1024/1024 decimal(8,2)) http_out_gb, cast(sum(t2.ftp_out)/1024/1024/1024 decimal(8,2)) ftp_out_gb, cast(sum(t2.smtp_out)/1024/1024/1024 decimal(8,2)) smtp_out_gb, cast(sum(t2.pop3_imap_out)/1024/1024/1024 decimal(8,2)) pop_imap_out_gb domains t1 join domainstraffic t2 on t1.id = t2.dom_id t2.date between '2014-02-01 00:00:00' , '2014-02-28 23:59:59' group domain;
example:
+--------------------------------+-------------+------------+-------------+-----------------+ | domain | http_out_gb | ftp_out_gb | smtp_out_gb | pop_imap_out_gb | +--------------------------------+-------------+------------+-------------+-----------------+ | abc.com | 0.00 | 0.00 | 0.08 | 0.39 | | def.com | 0.00 | 0.00 | 1.84 | 2.45 | | fgh.com | 0.00 | 0.00 | 0.00 | 0.00 |
what needed:
+--------------------------------+-------------+------------+-------------+-----------------+-----------------+ | domain | http_out_gb | ftp_out_gb | smtp_out_gb | pop_imap_out_gb | total | +--------------------------------+-------------+------------+-------------+-----------------+-----------------+ | abc.com | 0.00 | 0.00 | 0.08 | 0.39 | 0.47 | def.com | 0.00 | 0.00 | 1.84 | 2.45 | 4.29 | fgh.com | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
select domain, http_out_gb, ftp_out_gb, smtp_out_gb, pop_imap_out_gb, (http_out_gbftp_out_gb + smtp_out_gb + pop_imap_out_gb) total from( select t1.name domain, cast(sum(t2.http_out)/1024/1024/1024 decimal(8,2)) http_out_gb, cast(sum(t2.ftp_out)/1024/1024/1024 decimal(8,2)) ftp_out_gb, cast(sum(t2.smtp_out)/1024/1024/1024 decimal(8,2)) smtp_out_gb, cast(sum(t2.pop3_imap_out)/1024/1024/1024 decimal(8,2)) pop_imap_out_gb domains t1 join domainstraffic t2 on t1.id = t2.dom_id t2.date between '2014-02-01 00:00:00' , '2014-02-28 23:59:59' group domain)
Comments
Post a Comment