error : subquery must return only one column

Question

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?

@user2431581, You are defining field MyField via a sub query. When you do that the sub query must only return one value and one field. You are returning mat.mat as well as sum(stx.total). That is illegal SQL syntax. — Linger
– Linger, Commented Oct 29, 2013 at 3:52
Does this answer your question? subquery must return only one column — philipxy
– philipxy, Commented Apr 24, 2023 at 22:41

Erwin Brandstetter · Accepted Answer · 2023-08-01 20:00:20Z

29

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:

Aggregate columns with additional (distinct) filters

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

PostgreSQL LIKE query performance variations

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:

Pattern matching with LIKE, SIMILAR TO or regular expressions

edited Aug 1, 2023 at 20:00

answered Oct 29, 2013 at 5:23

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

András Aszódi Over a year ago

"Put a subquery that returns multiple columns in the FROM list and select from it.". This! Thanks a lot.

ely Over a year ago

What if the select is performed against a function that has multiple OUT parameters (but the function doesn't return a record, setof, or any similar type and the function can't be altered)? For example, suppose a function has 2 out parameters, 'hi' and 'lo' -- so that regular usage returns a record tuple like (4, 5). But "select *" would return the results separately as two columns. Now suppose you want to execute that function on multiple columns of the result of some join. Some thing like select my_func(a.colA, b.colB) from tabA a join tabB b on a.id = b.id.

Erwin Brandstetter Over a year ago

@Mr.F: This is beyond a comment. I suggest you start a new question with the necessary information. You can always link to this one for reference and drop a comment to link to the related question and notify me. There is a solution ...

ely Over a year ago

Good advice. Here is a link to the question.

Super Kai - Kazuya Ito · Accepted Answer · 2024-02-13 01:40:22Z

I created my_func() which returns two columns 2 and 3 as a subquery as shown below:

CREATE FUNCTION my_func() RETURNS INT AS $$
BEGIN         -- ↓  ↓
  RETURN (SELECT 2, 3);
END;
$$ LANGUAGE plpgsql;

But calling my_func() got the same error as shown below:

postgres=# SELECT my_func();
ERROR:  subquery must return only one column
LINE 1: (SELECT 2, 3)
        ^
QUERY:  (SELECT 2, 3)
CONTEXT:  PL/pgSQL function my_func() line 3 at RETURN

So, I returned only one column 2 as shown below:

CREATE FUNCTION my_func() RETURNS INT AS $$
BEGIN
  RETURN (SELECT 2);
END;
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

postgres=# SELECT my_func();
 my_func
---------
       2
(1 row)

postgres=# SELECT * FROM my_func();
 my_func
---------
       2
(1 row)

Or, I used row local variable whose type is RECORD as shown below:

CREATE FUNCTION my_func() RETURNS RECORD AS $$
DECLARE                        -- ↑ ↑ ↑
  row RECORD; -- Here
BEGIN
  SELECT * INTO row FROM (SELECT 2, 3);
  RETURN row; -- ↑
END;
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

postgres=# SELECT my_func();
 my_func
---------
 (2,3)
(1 row)

Marcio Mazzucato · Accepted Answer · 2017-11-11 17:56:13Z

0

Instead of subquery select statement

SELECT mat.mat  as mat, sum(stx.total)  as sumtotal

Try this statement

SELECT sum(stx.total)  as sumtotal

edited Nov 11, 2017 at 17:56

Marcio Mazzucato

9,36511 gold badges71 silver badges82 bronze badges

answered Oct 29, 2013 at 4:30

Snehal

1,09812 silver badges22 bronze badges

1 Comment

user2431581 Over a year ago

Try this state ment SELECT sum(stx.total) as sumtotal ERROR: more than one row returned by a subquery used as an expression

Collectives™ on Stack Overflow

error : subquery must return only one column

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related