2023年7月3日 23:07:13go评论84阅读模式

英文:

SQL count by column values

问题

下面是你要翻译的内容：

I'm trying to count multiple values in multiple coluns in a table and I did that:

TABLE

id	risk	record	api
1	OK	OK	NO
2	OK	OK	OK
3	OK	OK	OK
4	OK	NO	OK
5	NO	OK	NO

SQL:

select 'risk' as nm_test, risk as tst_result, count(*) as qty 
  from table
  group by risk 
union 
select 'record' as nm_test, record as tst_result, count(*) as qty
  from table
  group by record
union
select 'api' as nm_test, api as tst_result, count(*) as qty
  from table
  group by api

结果如下表所示：

nm_test	tst_result	qty
risk	OK	4
risk	NO	1
record	OK	4
record	NO	1
api	OK	3
api	NO	2

但是，相反地，我希望结果表格像这样旋转：

nm_test	OK	NO
risk	4	1
record	4	1
api	3	2

我尝试过，但是无法弄清楚窍门！任何帮助都将是宝贵的。

干杯。

英文:

I'm trying to count multiple values in multiple coluns in a table and I did that:

TABLE

id	risk	record	api
1	OK	OK	NO
2	OK	OK	OK
3	OK	OK	OK
4	OK	NO	OK
5	NO	OK	NO

SQL:

select &#39;risk&#39; as nm_test, risk as tst_result, count(*) as qty 
  from table
  group by risk 
union 
select &#39;record&#39; as nm_test, record as tst_result, count(*) as qty
  from table
  group by record
union
select &#39;api&#39; as nm_test, api as tst_result, count(*) as qty
  from table
  group by api

The result is the following table:

nm_test	tst_result	qty
risk	OK	4
risk	NO	1
record	OK	4
record	NO	1
api	OK	3
api	NO	2

But, instead, I would like to have the result table pivoted like this:

nm_test	OK	NO
risk	4	1
record	4	1
api	3	2

I tried to do that, but I was unable to figure out the trick! Any help would be valuable.

Cheers.

答案1

得分: 1

如果您有固定列要进行数据透视，值只有 'OK' 和 'NO'，则：

选择 'risk' 作为 nm_test，
        计算(case 当 risk = 'OK' 时 1 end) as 'OK',
        计算(case 当 risk = 'NO' 时 1 end) as 'NO'
从 mytable 中
联合全部
选择 'record',
        计算(case 当 record = 'OK' 时 1 end),
        计算(case 当 record = 'NO' 时 1 end)
从 mytable 中
联合全部
选择 'api',
        计算(case 当 api = 'OK' 时 1 end),
        计算(case 当 api = 'NO' 时 1 end)
从 mytable 中

英文:

If you have a fixed columns to pivot, and values are 'OK' and 'NO' only then :

select &#39;risk&#39; as nm_test, 
        count(case when risk = &#39;OK&#39; then 1 end) as &#39;OK&#39;,
        count(case when risk = &#39;NO&#39; then 1 end) as &#39;NO&#39;
from mytable
union all
select &#39;record&#39;, 
        count(case when record = &#39;OK&#39; then 1 end),
        count(case when record = &#39;NO&#39; then 1 end)
from mytable
union all
select &#39;api&#39;, 
        count(case when api = &#39;OK&#39; then 1 end),
        count(case when api = &#39;NO&#39; then 1 end)
from mytable

Demo here

答案2

得分: 0

尝试这个：

SELECT
  nm_test,
  SUM(CASE WHEN tst_result = 'OK' THEN qty ELSE 0 END) AS OK,
  SUM(CASE WHEN tst_result = 'NO' THEN qty ELSE 0 END) AS NO
FROM (
  SELECT 'risk' AS nm_test, risk AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY risk
  UNION ALL
  SELECT 'record' AS nm_test, record AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY record
  UNION ALL
  SELECT 'api' AS nm_test, api AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY api
) subquery
GROUP BY nm_test;

英文:

Try this:

SELECT
  nm_test,
  SUM(CASE WHEN tst_result = &#39;OK&#39; THEN qty ELSE 0 END) AS OK,
  SUM(CASE WHEN tst_result = &#39;NO&#39; THEN qty ELSE 0 END) AS NO
FROM (
  SELECT &#39;risk&#39; AS nm_test, risk AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY risk
  UNION ALL
  SELECT &#39;record&#39; AS nm_test, record AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY record
  UNION ALL
  SELECT &#39;api&#39; AS nm_test, api AS tst_result, COUNT(*) AS qty
  FROM mytable
  GROUP BY api
) subquery
GROUP BY nm_test;

答案3

得分: 0

忘记使用 UNION。

在 PostgreSQL 中，从列到行的数据透视可以通过将数据转换为 jsonb 类型来完成。这在将来需要添加更多测试时会很有帮助。

with pivot as (
  select j.*
    from mytable
   cross join lateral jsonb_each_text(to_jsonb(mytable) - 'id') 
                        as j(nm_test, tst_result)
)
select nm_test, 
       count(*) filter (where tst_result = 'OK') as ok,
       count(*) filter (where tst_result = 'NO') as no
  from pivot
 group by nm_test;

使用 - id 可以从结果中去掉该列。

在聚合函数上使用 filter 可以将数据透视回列。这在可能的结果有限且已知的情况下非常有用。

工作示例。

英文:

Forget using UNION.

Pivoting from columns to rows in PostgreSQL can be done by casting into and out of jsonb. This helps if you will be adding more tests in the future.

with pivot as (
  select j.*
    from mytable
   cross join lateral jsonb_each_text(to_jsonb(mytable) - &#39;id&#39;) 
                        as j(nm_test, tst_result)
)
select nm_test, 
       count(*) filter (where tst_result = &#39;OK&#39;) as ok,
       count(*) filter (where tst_result = &#39;NO&#39;) as no
  from pivot
 group by nm_test;

Using - id gets rid of that column from the results.

Using filter on the aggregates pivots back to columns. This is useful so long as the possible results are finite and known.

Working example.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

按列值统计的 SQL 计数

问题

答案1

答案2

答案3

SELECT * FROM OPENJSON — 未获得预期的值

如何在 PostgreSQL 中插入具有默认值的枚举字段？

如何在Java Spring中使Camel PgEvent组件正确使用DataSource。

将一个表中某列的唯一值复制到另一个具有不同结构的表中。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。