sql combine two subqueries

Question

I have two tables. Table A has an id column. Table B has an Aid column and a type column. Example data:

A:  id
    --
    1
    2

B: Aid | type
   ----+-----
   1   |  1
   1   |  1
   1   |  3
   1   |  1
   1   |  4
   1   |  5
   1   |  4
   2   |  2
   2   |  4
   2   |  3

I want to get all the IDs from table A where there is a certain amount of type 1 and type 3 actions. My query looks like this:

SELECT id 
FROM A
WHERE (SELECT COUNT(type)
       FROM B
       WHERE B.Aid = A.id
         AND B.type = 1) = 3 
  AND (SELECT COUNT(type)
       FROM B
       WHERE B.Aid = A.id
         AND B.type = 3) = 1

so on the data above, just the id 1 should be returned.

Can I combine the 2 subqueries somehow? The goal is to make the query run faster.

Answer 1

Does postgres support CTEs?

WITH counts (Counts, Type, Aid) as (
  select count(type), type
  from b group by Type, Aid
)
  select id
  from A
  join Counts B1 on b1.Aid = a.id and b1.type = 1
  join Counts B3 on b3.Aid = a.id and b3.type = 3
where
  b1.counts = 3 and b3.counts = 1

I'd suggest comparing the execution plans, but I suspect it would be similar since everything should get collapsed before execution.

Answer 2

This works in TSQL, does it work in Postgres?

SELECT A.ID
FROM A
WHERE A.ID in
(
SELECT AID
FROM B
GROUP BY AID
HAVING
  SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) = 3
  OR SUM(CASE WHEN Type = 3 THEN 1 ELSE 0 END) = 1
)

Answer 3

Another alternative:

SELECT DISTINCT Aid FROM (
   SELECT Aid,type,count(*) as n from B 
   GROUP BY Aid,type, ) as g 
 WHERE ( g.n=1 AND  g.type =  3 ) 
   OR  ( g.n=3 AND  g.type =  1 ) 

I doubt this will perform better than your original, though. You seem to be doing the best strategy: counting only the candidate rows. Perhaps some redundant prefiltering might help:

SELECT DISTINCT Aid FROM (
   SELECT Aid,type,count(*) as n from B
   WHERE g.type =  3 OR g.type =  1  -- prefilter 
   GROUP BY Aid,type, ) as g 
 WHERE ( g.n=1 AND  g.type =  3 ) 
   OR  ( g.n=3 AND  g.type =  1 ) 

Answer 4

Select ...
From A
    Join    (
            Select B.Id
                , Sum ( Case When B.Type = 1 Then 1 Else 0 End ) As Type1Count
                , Sum ( Case When B.Type = 3 Then 1 Else 0 End ) As Type3Count
            From B
            Where B.Type In(1,3)
            Group By B.Id
            ) As Z
        On Z.Id = A.Id
Where Z.Type1Count = 3
    And Z.Type3Count = 1