用分组列计数 [英] Count with a grouping columns

问题描述

我的请求很简单，我计算了表格的差异列。
第一个查询计算子查询带来的列：

My request is simple, I count the differents columns of my table. The first query count the columns brought by the subquery:

SELECT COUNT(field1), COUNT(field2), COUNT(field3)
FROM (
    SELECT field1, field2, field3
    FROM mytable
    WHERE ...
    ORDER BY id
    LIMIT 500000
) AS rq

在我的用例中，Limit子句至关重要。

The Limit clause is essential in my use case.

此请求非常适合计数单打列，但我还需要对一组列进行计数（很多非空列而不是单个列）。

This request works very well for counting singles columns, but I need also count a grouping of columns (a lot of not null columns instead of a single one).

所以我需要的结果

SELECT COUNT(1) AS mygroup
FROM mytable
WHERE (field4 IS NOT NULL AND field5 IS NOT NULL AND field6 IS NOT NULL)

在第一个查询的当前500000（LIMIT子句）行中。

But in the current 500000 (LIMIT clause) lines from the first query.

我该怎么做？

感谢您的帮助

推荐答案

只需使用条件聚合：

SELECT COUNT(field1), COUNT(field2), COUNT(field3),
       SUM(CASE WHEN field4 IS NOT NULL AND field5 IS NOT NULL AND field6 IS NOT NULL THEN 1 ELSE 0 END)
FROM (SELECT t.*
      FROM mytable t
      WHERE ...
      ORDER BY id
      LIMIT 500000
     ) rq;

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