CODEWITHSUNDEEP
mysql
MrSchism
MrSchism

Reputation: 43

Grouping/summing arbitrary field

I've tried a few different solutions I've found with no luck. The table I'm giving is an example that follows a similar design and function to the one I'm actually working with:

PK    |    Color    |  Count
----------------------------
1         Blue          4
2         Cyan          6 
3         Forest        3
4         Green         2
5         Indigo        5
6         Navy         12
7         Pink          8
8         Purple        7
9         Red           9
10        Violet        1

In this case, I want to break this up into color groups and list the sum. For example, the group "Blues" would contain Blue, Cyan, Indigo, and Navy and would be a count of 27. "Purples" would be 8 (Purple/Violet), "Greens" would be 5 (Forest/Green) and so on.

Given the information at hand, how would you do this?

Upvotes: 0

Views: 30

Answers (1)

juergen d
juergen d

Reputation: 204756

select sum(case when color in ('Blue','Cyan','Indigo','Navy') then Count end) as Blues_count,
       sum(case when color in ('Purple','Violet') then Count end) as Purples_count,
       sum(case when color in ('Forest','Green') then Count end) as Greens_count
from your_table

Upvotes: 2

Related Questions