How to sum a column based on a non-mutually exclusive condition?-CodePudding

Say I have data as:

TextColumn | ValueColumn
-----------|------------
a          | 1
a          | 1
ab         | 1
b          | 2
b          | 2

How can I get the following result?

SELECT CASE WHEN TextColumn LIKE '%a%' THEN 'a' WHEN TextColumn LIKE '%b%' THEN 'b' END AS TextGroup, SUM(ValueColumn) AS GroupSum FROM <Table>
GROUP BY TextGroup

This should return:

TextGroup | GroupSum
----------|---------
a         | 3
b         | 5

CodePudding user response：

Assuming this is an abstract and you will be defining your TextGroup values

Example

Select TextGroup 
      ,GroupSum = sum(ValueColumn)
 From  (values  ('a')
               ,('b')
      )A(TextGroup)
 Join YourTable B on charindex(TextGroup,[TextColumn])>0
 Group By TextGroup

Results

TextGroup   GroupSum
a           3
b           5

EDIT: Another option

Declare @TextGroups varchar(max) = 'a,b'
 
Select TextGroup = A.value
      ,GroupSum = sum(ValueColumn)
 From  string_split(@TextGroups,',') A
 Join YourTable B on charindex(A.value,[TextColumn])>0
 Group By A.value

Note:
You can use a LEFT JOIN if you want to see the missed values (NULL's)

CodePudding user response：

Consider below approach (BigQuery)

select TextGroup, sum(ValueColumn) as GroupSum
from your_table, unnest(split(TextColumn, '')) TextGroup
group by TextGroup

if applied to sample data in your question - output is