多列条件计数SQL [英] Multiple Column Conditional Count SQL

问题描述

我正在尝试对连续4个单独的列中的不同条目进行计数，然后将结果总计.

I am trying to count distinct entries in 4 separate columns in a row and then total the results.

例如，表头看起来类似于:

For instance the table headers look similar to this:

ID       Col1    Col2    Col3    Col4

每个列(保存ID)可以具有文本值W，X，Y或Z.列可以具有相同的值.

Each column (save ID) can have a text value W, X, Y, or Z. Columns can have the same value.

我想做的是找到一种计算列中每个条目的方法，但是每行只对W，X，Y和Z进行一次计数.因此，如果:

What I am trying to do is figure a method for counting each entries in the columns, but only count W, X, Y, and Z once per row. So if:

ID       Col1    Col2    Col3    Col4
          X        X       Y
          Y        W       X
          Z        Y       Y

结果表将是:

    Value    Count
      W        1
      X        2
      Y        3
      Z        1

任何帮助将不胜感激.

推荐答案

也许我遗漏了一些东西，但这很简单吗:

Perhaps I'm missing something, but would this be as simple as:

Select Val, Count(*)
From    (
        Select Id, Col1 As Val From Table1
        Union Select Id, Col2 From Table1
        Union Select Id, Col3 From Table1
        Union Select Id, Col4 From Table1
        ) As Z
Where Z.Val Is Not Null
Group BY Z.Val

没有理由同时使用Distinct和Union，因为Union将使结果不同.因此，我们需要为每一行(Id)包括唯一值.

There is no reason to use Distinct and Union together as Union will make the results distinct. Because of that, we need to include the unique value for each row (Id).

SQL提琴(这使用SQL Server，但相同的语法将在MS Access)

SQL Fiddle (This uses SQL Server but the same syntax will work in MS Access)

这篇关于多列条件计数SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！