Given this table:

Given this table:

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.

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.

• mysql

• 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

1 Answer 1

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.