CODEWITHSUNDEEP
mysql
Wistar
Wistar

Reputation: 3790

MySQL SUM number within string

I have a movies table

SELECT * FROM movies

+-----------------------------------+---------------------------------------------------------+
|            MovieTitle             |                         Awards                          |
+-----------------------------------+---------------------------------------------------------+
| Rocky 20                          | Nominated for 1 Oscar. Another 37 wins & 46 nominations |
| Die Hard 51                       | Won 1 Oscar. Another 5 wins & 19 nominations.           |
| Killer tomatoes                   | 9 nominations.                                          |
+-----------------------------------+---------------------------------------------------------+

I would like to be able to SUM all the numbers of award/nominations in Awards such as to have something like this:

    +-----------------------------------+---------------------------------------------------------+-------+
    |            MovieTitle             |                         Awards                          | Total |
    +-----------------------------------+---------------------------------------------------------+-------+
    | Rocky 20                          | Nominated for 1 Oscar. Another 37 wins & 46 nominations |    84 |
    | Die Hard 51                       | Won 1 Oscar. Another 5 wins & 19 nominations.           |    25 |
    | Killer tomatoes                   | 9 nominations.                                          |     9 |
    +-----------------------------------+---------------------------------------------------------+-------+

Any suggestions on how I could achieve that with only MySQL?

Upvotes: 0

Views: 1491

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Your strings are all looking for the number before "nominations", "wins" and "Oscars". This simplifies the problem.

You can get the value that you want using string manipulation. Here is one solution:

select ((case when awards like '% nominations%'
              then substring_index(substring_index(awards, ' nominations', 1), ' ', -1) + 0
              else 0
         end) +
        (case when awards like '% wins%'
              then substring_index(substring_index(awards, ' wins', 1), ' ', -1) + 0
              else 0
         end) +
        (case when awards like '% Oscar%'
              then substring_index(substring_index(awards, ' Oscar', 1), ' ', -1) + 0
              else 0
         end)
        ) as TotalMentions

I can appreciate having to deal with poorly formed data in a relational database. In this case, the strings are pretty well formed, so the solution is not that unreasonable.

Upvotes: 2

BlackVikingPro
BlackVikingPro

Reputation: 27

SELECT SUM(column_name) FROM table_name;

or

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

I hope this helps!

Upvotes: -1

Related Questions