How to do a Select in another Select with Postgresql

Question

I must do this query in another Select in Postgresql :

SELECT COUNT(tn.autoship_box_transaction_id) 
FROM memberships.autoship_box_transaction tn 
WHERE tn.autoship_box_id = b.autoship_box_id

Do I must use the clause WITH ?

Answer 1

As long as the query produces a single data element, you can use it in place of an attribute:

SELECT (
          SELECT COUNT(tn.autoship_box_transaction_id) 
            FROM memberships.autoship_box_transaction tn 
           WHERE tn.autoship_box_id = b.autoship_box_id
       ) AS cnt
     , other_column
  FROM wherever
     ;

Have a look at this SQL fiddle demonstrating the use case.

This method often comes with a performance penalty if the db engine actually iterates over the result set and performs the query on each record encountered.

The db engine's optimizer may be smart enough to avoid the extra cost (and it should in the fiddle's toy example), but you have to look at the explain plan to be sure.

Note that its mostly an issue with 'correlated subqueries', ie. queries embedded as shown which depend on the embedding. Your example example appears to be of this kind as you use a table alias b which isn't defined anywhere.

There might be the option of moving the subselect to the from clause (beware: This statement is for explanatory purposes only; you must adapt it to your use case, I am just wild guessing here):

SELECT stats.cnt
     , b.other_column
  FROM b_table b
  JOIN (
          SELECT COUNT(tn.autoship_box_transaction_id) cnt
               , tn.autoship_box_id
            FROM memberships.autoship_box_transaction tn 
        GROUP BY tn.autoship_box_id
       ) stats
    ON (stats.autoship_box_id = b.autoship_box_id)
     ;