-1

I have a query that returns the following data:

SELECT project_id, value FROM value_table;

project_id  value
01          $100,000.00 | $200,000.00
02          $100,000.00
03          $200,000.00 | $200,000.00 | $200,000.00

I would like to obtain:

  1. The sum of the values for each project (so for project 01 that means 300,000.00)
  2. The sum of all values of all projects (1,000,000.00 in this example)

MySQL Version: 5.7.29

It's possible to do this using mysql ?

forpas
  • 160,666
  • 10
  • 38
  • 76
drak
  • 87
  • 1
  • 1
  • 8

1 Answers1

1

You should defintely fix your schema and store each value on a separate row rather than as a delimited list. See this famous SO post for a discussion on how bad it is to store delimited lists in a relational database.

That said, here is a solution to parse the delimited lists using a recursive query (available in MySQL 8.0 only). You can then aggregate by project:

with recursive cte as (
    select 
        project_id,
        replace(substring(concat(value, ' | '), 2, locate(' | ', concat(value, ' | '))), ',', '') val,
        substring(concat(value, ' | '), locate(' | ', concat(value, ' | ')) + 3) value
    from mytable
    union all
    select
        project_id,
        replace(substring(value, 2, locate(' | ', value)), ',', ''),
        substring(value, locate(' | ', value) + 3)
    from cte
    where locate(' | ', value) > 0
)
select project_id, sum(val) total_val
from cte
group by project_id
order by project_id

Demo on DB Fiddle:

project_id | total_val
---------: | --------:
         1 |    300000
         2 |    100000
         3 |    600000

If you want the total value for all projects, you can just change the outer query to:

select sum(val) total_val from cte
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I agree that properly storing it as values individually would be much better however I do not control that so that's why I'm having to workaround it. Thanks for your comment! – drak May 10 '20 at 22:11