对任意字段进行分组/求和 [英] 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

在这种情况下，我想将其分解为颜色组并列出总和.例如，Blues"组将包含 Blue、Cyan、Indigo 和 Navy，计数为 27.Purples"将为 8(紫色/紫罗兰色)，Greens"将为 5(森林/绿色)和以此类推.

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?

推荐答案

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

这篇关于对任意字段进行分组/求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！