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

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

CodePudding user response：

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))

CodePudding user response：

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