Question 21372 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I group together string rows with partial matching strings and then sum them together in SQL? – Mysql

Artist Points
Notorious BIG 34
Notorious BIG feat. blah blah 42
2pac 20
2pac feat. Dr. Dre 30

I would like to group and sum by Artist for a table to look like so:

Artist Points
Notorious BIG 76
2pac 50

P.S. there are a lot of artists featuring other artists in this dataset so I cannot just do them all individually. Thanks for your help.

I was thinking maybe I should CREATE VIEW with all the feat.% removed then group the artists together? Not sure how I would go about doing that either.

CREATE VIEW no_feat AS
SELECT 
    REPLACE(artist, 'ft%', ' ')
FROM rankings;

I’m very new at this so I tried that and it obviously did not work. I dont think the replace function accepts wildcards

Tags:

2

Answers


  1. 0

    Considering the Artist starting with same string need to be grouped together, the below could work:

    Select SUBSTRING(Artist, 0,CHARINDEX(' ', Artist, 0)), SUM(Points)
From Artist
Group by SUBSTRING(Artist, 0,CHARINDEX(' ', Artist,0))
    Login or Signup to reply.
  2. 0

    You could use substring_index for this case.

    This would get all the values before feat., if feat is missing entire string will returned.

    SELECT SUBSTRING_INDEX(artist, 'feat.', 1) as Artist,
       sum(points) as Points
FROM my_table
GROUP BY  SUBSTRING_INDEX(artist, 'feat.', 1);

    But, if the string contain spaces you should use TRIM as follows,

    SELECT TRIM(SUBSTRING_INDEX(artist, 'feat.', 1)) as Artist,
       sum(points) as Points
FROM my_table
GROUP BY  TRIM(SUBSTRING_INDEX(artist, 'feat.', 1));

    Result:

    Artist           Points
Notorious BIG     76
2pac              50

    https://dbfiddle.uk/QpaO9bVZ

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search