最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

mysql - how to sum rows together but also leave the original rows in the results? - Stack Overflow

programmeradmin3浏览0评论

Given this table:

BUM product_line revenue gp labor cm materials inventory oi company last_updated_date
MT covers 129541.4 0 36670.17 0 0 0 0 hen 1/28/2025
MT bellows 146123.9 0 40632.67 0 0 0 0 hen 1/29/2025
MT repairs 141234 0 37891.91 0 0 0 0 hen 1/29/2025
MT cable chain 133876.6 0 26732.87 0 0 0 0 hen 1/29/2025
MT flex 123456.1 0 19234.76 0 0 0 0 hen 1/29/2025
DS coneyors 245671.9 0 78983.24 0 0 0 0 hen 1/30/2025

Given this table:

BUM product_line revenue gp labor cm materials inventory oi company last_updated_date
MT covers 129541.4 0 36670.17 0 0 0 0 hen 1/28/2025
MT bellows 146123.9 0 40632.67 0 0 0 0 hen 1/29/2025
MT repairs 141234 0 37891.91 0 0 0 0 hen 1/29/2025
MT cable chain 133876.6 0 26732.87 0 0 0 0 hen 1/29/2025
MT flex 123456.1 0 19234.76 0 0 0 0 hen 1/29/2025
DS coneyors 245671.9 0 78983.24 0 0 0 0 hen 1/30/2025

I am trying to get something like the results below in MySQL. I need to sum up rev,gp,labor, and cm for BUM - MT's rows into one product line (all) but also keep all his other product lines in the results.

BUM product_line revenue gp labor cm materials inventory oi company last_updated_date
MT covers 129541.4 0 36670.17 0 0 0 0 hen 1/28/2025
MT bellows 146123.9 0 40632.67 0 0 0 0 hen 1/29/2025
MT repairs 141234 0 37891.91 0 0 0 0 hen 1/29/2025
MT cable chain 133876.6 0 26732.87 0 0 0 0 hen 1/29/2025
MT flex 123456.1 0 19234.76 0 0 0 0 hen 1/29/2025
DS coneyors 245671.9 0 78983.24 0 0 0 0 hen 1/30/2025
MT all 674231.9 0 161162.4 0 0 0 0 hen 1/30/2025

I tried:

select bum, 
    case when product_line IN ('covers','repairs','bellows','flex','cable chain') 
        then 'all' else product_line 
    end as product_line, sum(revenue) as revenue 
from wpc3_bsd_ops_data 
group by bum

however that doesn't return MT's product lines. I understand why but I'm not sure how to construct a query to get the results I need. Any help is greatly appreciated.

Share Improve this question edited Jan 30 at 20:54 Barmar 783k56 gold badges547 silver badges660 bronze badges asked Jan 30 at 19:44 ProfoundHypnoticProfoundHypnotic 565 bronze badges 6
  • This is just a UNION. Write a subquery that gets the total revenue, and combine it with UNION to the original table. But where does labor=161162.4 come from in your desired result? – Barmar Commented Jan 30 at 21:07
  • Why isn't there a total row for DS? – Barmar Commented Jan 30 at 21:09
  • @Barmar they specifically only wanted a total row for the MT BUM. not clear if that should be included even when there are no MT rows – ysth Commented Jan 30 at 21:13
  • now I see, 161162.4 is the total of labor for all the other BUM=MT rows. – Barmar Commented Jan 30 at 21:15
  • Why do you have last_updated_date=1/30/2025 in the all` row? That doesn't come from any of the MT rows. – Barmar Commented Jan 30 at 21:19
 |  Show 1 more comment

1 Answer 1

Reset to default 0

Use UNION to add a row with the totals for BUM='MT'

SELECT *
FROM wpc3_bsd_ops_data
UNION ALL
SELECT 'MT', 'all', SUM(revenue), SUM(gp), SUM(labor), SUM(cm), SUM(materials), SUM(inventory), SUM(oi), MAX(company), CURDATE()
FROM wpc3_bsd_ops_data
WHERE BUM = 'MT'

It's not clear what you really want in the company column if there are multiple companies. I chose MAX() arbitrarily. You could also just leave that column blank if it's not meaningful.

The date in your all line doesn't seem to come from the table contents, so I assumed you want the current date.

发布评论

评论列表(0)

  1. 暂无评论