PostgreSQL count multiple columns of the same table

Question

I want to count some columns from a table in PostgreSQL.

For each column count I have some conditions and I want to have all in one query. My problem is the fact that I don't get the expected results in counting because I tried to apply all the conditions for the entire data set.

The table:

column1	column2	column3
UUID10	UUID20	UUID30
`NULL`	UUID21	`NULL`
`NULL`	UUID22	UUID31
UUID11	UUID20	UUID30

This is what I tried so far:

SELECT
   COUNT(DISTINCT column1) AS column1_count,
   COUNT(DISTINCT column2) AS column2_count,
   COUNT(DISTINCT column3) AS column3_count
FROM TABLE
WHERE 
   column2 IN ('UUID20', 'UUID21', 'UUID22')       
   AND column1 = 'UUID10'  -> this condition should be removed from this where clause
   OR column3 IN ('UUID30', 'UUID31')

Result:

column1_count	column2_count	coumn3_count
2	3	2

The result in not correct because I should have column1_count = 1. I mean, this is what the query does, but is not what I intended. So I thought to have some constrains for column2 and column3 in a subquery, and having a another condition just for column1.

A second try:

SELECT *
FROM 
(
    SELECT 
    column1
    column2,
    column3
    FROM TABLE
    WHERE  
    column2 IN ('UUID20', 'UUID21', 'UUID22') 
    OR column3 IN ('UUID30', 'UUID31')
) x
WHERE
column1 = 'UUID10'

Result:

column1_count	column2_count	coumn3_count
1	1	1

Because the last condition on column1 is restricting my result, I end up having 1 for all the counts. How can I apply different conditions for counting each column?

I would try not to use UNION if is possible. Maybe there can be made some subqueries in another way than what I tried so far. I just have to find a way for the constraint for the column1, to not be on the same WHEN clause as for the column2 and column3.

user_1144035 · Answer

I think you want conditional aggregation:

SELECT COUNT(DISTINCT CASE WHEN column1 = 'UUID10' THEN column1 END) AS column1_count,
       COUNT(DISTINCT column2) AS column2_count,
       COUNT(DISTINCT column3) AS coumn3_count
FROM TABLE
WHERE column2 IN ('UUID20', 'UUID21', 'UUID22') OR      
      column3 IN ('UUID30', 'UUID31');

I assume that you are aware that COUNT(DISTINCT CASE WHEN column1 = 'UUID10' THEN column1 END) is not particularly useful code. It returns 1 or 0 depending on whether the value is present. I assume your code is actually more interesting.

PostgreSQL count multiple columns of the same table

1 Answers