Search code examples
mysql

how to sum rows together but also leave the original rows in the results?

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.

Solution

  • 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.