I have a table A:
entity_id name
------------------
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5
6 Test6
I have a table B:
entity_id value1 value2
-----------------------------
1 10 20
1 15 30
2 10 25
1 9 45
3 null 1
2 45 50
3 20 null
I need to write a single query to select the entity_id and name from Table A and count the total occurrences for an entity_id of columns value1 and value2 from Table B and then the total of those column counts (null doesn't count).
So my output table would be:
entity_id name value1_count value2_count total_count
----------------------------------------------------------------------
1 Test1 3 3 6
2 Test2 1 2 3
3 Test3 1 1 2
4 Test4 0 0 0
5 Test5 0 0 0
6 Test6 0 0 0
I am having trouble summing the count of value1 and count of value2 and outputting that value in the total_count per unique entity_it.
This is the query I have so far:
SELECT DISTINCT a.entity_id, a.name
, count(b.value1) AS value1_count, count(b.value2) AS value2_count, sum(2) AS total_count
FROM a
LEFT JOIN b ON a.entity_id = b.entity_id
GROUP BY a.entity_id, a.name
I know that the sum(2) as total_count is incorrect and doesn't get me what I want.
CodePudding user response：
try this :
WITH list AS
(
SELECT b.entity_id
, count(*) FILTER (WHERE b.value1 IS NOT NULL) OVER () AS value1_count
, count(*) FILTER (WHERE b.value2 IS NOT NULL) OVER () AS value2_count
FROM Table_B AS b
GROUP BY b.entity_id
)
SELECT a.entity_id, a.name
, COALESCE(l.value1_count, 0)
, COALESCE(l.value2_count,0)
, COALESCE(l.value1_count l.value2_count, 0) AS total_count
FROM Table_A AS a
LEFT JOIN list AS l
ON a.entity_id = l.entity_id
CodePudding user response：
SELECT entity_id, a.name
, COALESCE(b.v1_ct, 0) AS value1_count
, COALESCE(b.v2_ct, 0) AS value2_count
, COALESCE(b.v1_ct b.v2_ct, 0) AS total_count
FROM a
LEFT JOIN (
SELECT entity_id, count(value1) AS v1_ct, count(value2) AS v2_ct
FROM b
GROUP BY 1
) b USING (entity_id);
db<>fiddle here
Aggregate first, join later. That's simpler and faster. See:
count() never produces NULL. Only the LEFT JOIN can introduce NULL values for counts in this query, so v1_ct and v2_ct are either both NULL or both NOT NULL. Hence COALESCE(v1_ct v2_ct, 0) is ok. (Else, one NULL would nullify the other summand in the addition.)