CODEWITHSUNDEEP
sqlpostgresql
Blackdynomite
Blackdynomite

Reputation: 431

SQL: Group by Case Statement to sum Row Values

I have a table that looks like below. I am trying to group by subject using a case statement for every "subject" that starts with "cf" like something like the following:

SELECT CASE WHEN subject LIKE '%cf' THEN sum(Count)
FROM table

The goal is to just drop all the extra characters following "cf" and then sum the total count and create a new row as 'Cf' with the sum of count value as shown in the desired output.

subject.  count
status    2461193
priority  1042073
ta.       126295
dueDate   62560
assignee  34142
cf2122     1
cf2123.    1
cf4312.    1
cf3234.    1 
cf5464.    1

desired output:

subject.  count
status    2461193
priority  1042073
ta.       126295
dueDate   62560
assignee  34142
cf.       1221

Upvotes: 0

Views: 417

Answers (1)

user330315
user330315

Reputation:

You want a GROUP BY based on an expression. You also need to put the wildcard for the LIKE condition after the prefix you want, 'cf%'

SELECT CASE 
          WHEN subject LIKE 'cf%' THEN 'cf' 
          else subject
       end as normalized_subject,
       sum("count")
FROM the_table
group by normalized_subject
order by sum("count") desc

Using a column alias in the GROUP BY is a Postgres extension to the SQL language. In other DBMS products you would need to repeat the CASE expression in the GROUP BY (which is only more to type, but won't change performance)

Online example

Upvotes: 3

Related Questions