Macbernie Macbernie - 5 months ago 7
SQL Question

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


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).

So I need the result of

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


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

How can I do that ?

Thanks for help

Answer

Just use conditional aggregation:

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;