基于SQL server中具有相同值的2列的值的总和或计数

I have a DB table which has columns named with few more other columns

我有一个DB表，它的列用很少的其他列命名

ColorA
ColorB
Status

The data in this DB look like this.

这个DB中的数据是这样的。

ColorA     ColorB    Status
---------     ---------    ---------
GREEN           NULL       YES
GREEN           NULL       YES
RED             GREEN      NO
RED             GREEN      YES

The result what I want is something like this depending on Status='YES'

我想要的结果是这样的取决于状态='YES'

Color  Count
GREEN   3
RED     1

I have also defined table which hold all the color.

我还定义了包含所有颜色的表。

How to construct the SQL query for this which will result in the output as mentioned earlier? I have a query but I am using LEFT Join and then doing an UNION which is not giving proper result.

如何为此构造SQL查询，从而产生前面提到的输出?我有一个查询，但我使用左连接，然后做一个没有给出正确结果的联合。

3 个解决方案

#1

This should work:

这应该工作:

SELECT a.color, 
       Count(a.color) AS Count 
FROM   (SELECT colora AS color 
        FROM   table1 
        WHERE  status = 'YES' 
               AND colora IS NOT NULL 
        UNION ALL 
        SELECT colorb 
        FROM   table1 
        WHERE  status = 'YES' 
               AND colorb IS NOT NULL) a 
GROUP  BY a.color

Result

结果

| COLOR | COUNT |
-----------------
| GREEN |     3 |
|   RED |     1 |

See the demo

看到演示

#2

From the example you have given you want to know the count of each ColorA or ColorB record

从您给出的示例中，您希望知道每个ColorA或ColorB记录的计数

SELECT Color, SUM(Total) AS Count
FROM (
    SELECT ColorA as Color, SUM(CASE WHEN Status = 'Yes' THEN 1 ELSE 0 END) AS Total
    Group By ColorA
UNION
    SELECT ColorB as Color, SUM(CASE WHEN Status = 'Yes' THEN 1 ELSE 0 END) AS Total
    Group By ColorB
) U
GROUP BY Color

#3

This works in SQL Server, MySQL and PostgreSQL (SQLFiddle demo):

这适用于SQL Server、MySQL和PostgreSQL (SQLFiddle demo):

SELECT color, sum(cnt) AS count FROM (
    SELECT colorA AS color, count(*) AS cnt
    FROM mytable
    WHERE status = 'YES'
    GROUP BY colorA
UNION ALL
    SELECT colorB AS color, count(*) AS cnt
    FROM mytable
    WHERE status = 'YES'
    GROUP BY colorB
) AS x
WHERE color IS NOT NULL  
GROUP BY color

#1