sqldatabasepostgresqlleft-joincross-join

error : subquery must return only one column

I am getting the error subquery must return only one column when I try to run the following query:

SELECT mat.mat as mat1, sum(stx.total) as sumtotal1,
  (
    SELECT mat.mat  as mat, sum(stx.total) as sumtotal
    FROM stx 
      LEFT JOIN mat ON stx.matid = mat.matid
      LEFT JOIN sale ON stx.saleid = sale.id
    WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31' 
      AND sale.userid LIKE 'A%'
    GROUP BY mat.mat
  ) AS MyField
FROM stx 
  LEFT JOIN mat ON stx.matid = mat.matid
  LEFT JOIN sale ON stx.saleid = sale.id
WHERE stx.date BETWEEN '2013-05-01' AND '2013-08-31'
  AND sale.userid LIKE 'B%'
GROUP BY mat.mat

What is causing this error?

Solution

  • Put a subquery that returns multiple columns in the FROM list and select from it.

    A correlated subquery would be a bad idea to begin with. However, your query is not even correlated (no link to outer query) and seems to return multiple rows. This leads to a (possibly very expensive and nonsensical) cross join producing a Cartesian product.

    Looks like you really want something like this:

    SELECT m1.mat AS mat1, m1.sumtotal AS sumtotal1
     , m2.mat AS mat2, m2.sumtotal AS sumtotal2
FROM  (
   SELECT mat.mat, sum(stx.total) AS sumtotal
   FROM   stx 
   LEFT   JOIN mat ON mat.matid = stx.matid
   LEFT   JOIN sale ON stx.saleid = sale.id
   WHERE  stx.date BETWEEN '2013-05-01' AND '2013-08-31'
   AND    sale.userid LIKE 'A%'
   GROUP  BY mat.mat
   ) m1
JOIN  (
   SELECT mat.mat, sum(stx.total) AS sumtotal
   FROM   stx 
   LEFT   JOIN mat ON mat.matid = stx.matid
   LEFT   JOIN sale ON sale.id = stx.saleid
   WHERE  stx.date BETWEEN '2013-05-01' AND '2013-08-31' 
   AND    sale.userid LIKE 'B%'
   GROUP  BY mat.mat
   ) m2 USING (mat);

    Both LEFT JOIN are also pointless. The one on sale is forced to a INNER JOIN by the WHERE condition. The one on mat seems pointless, since you GROUP BY mat.mat - except if you are interested in mat IS NULL? (I doubt it.)

    The whole query can further simplified to:

    SELECT m.mat
     , sum(x.total) FILTER (WHERE s.userid LIKE 'A%') AS total_a
     , sum(x.total) FILTER (WHERE s.userid LIKE 'B%') AS total_b
FROM   sale s 
JOIN   stx  x ON x.saleid = s.id
JOIN   mat  m ON m.matid = x.matid
WHERE  s.userid LIKE 'ANY ('{A%,B%}')
AND    x.date BETWEEN '2013-05-01' AND '2013-08-31'
GROUP  BY 1;

    Using the aggregate FILTER clause (Postgres 9.4+). See:

    In Postgres 11 or later, this can be optimized further with the "starts with" operator ^@ for prefix matching. See:

    SELECT m.mat
     , sum(x.total) FILTER (WHERE s.userid ^@ 'A') AS total_a
     , sum(x.total) FILTER (WHERE s.userid ^@ 'B') AS total_b
FROM   sale s 
JOIN   stx  x ON x.saleid = s.id
JOIN   mat  m ON m.matid = x.matid
WHERE  s.userid ^@ ANY ('{A,B}')  
AND    x.date BETWEEN '2013-05-01' AND '2013-08-31'
GROUP  BY 1;

    The WHERE condition might get even simpler, depending on your secret data types and indices. Here is an overview over pattern matching operators: