Error: Subquery Must Return Only One Column

error : subquery must return only one column

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, but unrelated (no link to outer query) and seems to return multiple rows. This leads to (possibly very expensive and nonsensical) cross join producing a cartesian product, probably not your (secret) intention.

Looks like you really want:

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 case can probably be further simplified to:

SELECT m.mat
      ,sum(CASE WHEN s.userid LIKE 'A%' THEN x.total END) AS total_a
      ,sum(CASE WHEN s.userid LIKE 'B%' THEN x.total END) 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 'A%' OR s.userid LIKE 'B%')
AND    x.date BETWEEN '2013-05-01' AND '2013-08-31'
GROUP  BY 1;

The WHERE condition can probably be simplified further, depending on your secret data types and indices. A boatload of information on precisely that case in this related answer on dba.SE.

I get an ERROR subquery must return only one column. How do I solve this?

This appears to be the line causing the error:

and "Candidates"."Confirmed" = (select max("Confirmed")"Confirmed", "SubstituteTeacher"
    from dbo."Candidates" group by "SubstituteTeacher")

First of all, it doesn't make sense to compare a single scalar value to a record having two values. But, worse than this, the subquery on the RHS is probably returning more than one record. Try changing to this:

AND "Candidates"."Confirmed" IN (SELECT MAX("Confirmed") FROM dbo."Candidates"
                                 GROUP BY "SubstituteTeacher")

Here is your entire query refactored with this problem fixed:

SELECT
    COUNT(DISTINCT wc.SubstituteTeacher) AS Count,
    a.FirstName,
    a.LastName,
    c.Confirmed,
    c.FinalStatus
FROM dbo.Accounts a
LEFT JOIN dbo.WorkContracts wc
    ON a.ID = wc.SubstituteTeacher
FULL OUTER JOIN dbo.SubstituteTeacherPeriods s
    ON a.ID = s.CreatedBy
FULL OUTER JOIN dbo.Candidates c
    ON a.ID = c.ModifiedBy
WHERE
    wc.WorkContractType != 3 AND
    s.StartTime >= '2018-09-18 00:00:00' AND s.EndTime < '2018-09-19 00:00:00' AND
    s.Discriminator = 'AvailabilityPeriod' AND
    c.FinalStatus = (SELECT MAX(FinalStatus) FROM dbo.Candidates) AND
    c.Confirmed IN (SELECT MAX(Confirmed) FROM dbo.Candidates
                             GROUP BY SubstituteTeacher)
GROUP BY
    a.FirstName,
    a.LastName,
    c.FinalStatus,
    c.Confirmed;

Note that by introducing table aliases and removing the unnecessary double quotes everywhere, the query becomes much more readable.

If you want the latest record for each teacher using the Confirmed column then we can use ROW_NUMBER:

WITH cte AS (
    SELECT
        COUNT(DISTINCT wc.SubstituteTeacher) AS Count,
        a.FirstName,
        a.LastName,
        c.Confirmed,
        c.FinalStatus,
        ROW_NUMBER() OVER (PARTITION BY a.FirstName, a.LastName ORDER BY Confirmed DESC) rn
    FROM dbo.Accounts a
    ...         -- the rest of the above query
)

SELECT
    Count,
    FirstName,
    LastName,
    Confirmed,
    FinalStatus
FROM cte
WHERE rn = 1;

PostgreSQL error: subquery must return only one column

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.users As l
CROSS JOIN (SELECT * FROM stage.dogs LIMIT 1) as g

This is literally what you had (assuming stage.dogs) is not empty. Not sure if there should be a correlation between users and dogs though.

---

To find the closest dog to a user, you can use this query. The scalar subquery finds the dog's ID, and is joined back to the table to retrieve the other columns.

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM (
    SELECT l1.*, (SELECT g1.id
                  FROM stage.dogs as g
                  ORDER BY g.geom <-> l.geom 
                  LIMIT 1) g_id
    FROM stage.users As l1
) l
JOIN stage.dogs as g ON g.id = l.g_id;

Fair warning that this will NOT be a fast query.

---

At the risk of performing even slower, see query below for multiple tables

SELECT l.id, l.lat, l.lng, l.geom,
       g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom) dog_distance,
       c.id, c.lat, c.lng, ST_Distance(l.geom, c.geom) cat_distance,
       b.id, b.lat, b.lng, ST_Distance(l.geom, b.geom) bird_distance
FROM (
    SELECT l1.*, (SELECT g1.id
                  FROM stage.dogs as g1
                  ORDER BY g1.geom <-> l.geom 
                  LIMIT 1) dog_id,
                 (SELECT c1.id
                  FROM stage.cats as c1
                  ORDER BY c1.geom <-> l.geom 
                  LIMIT 1) cat_id,
                 (SELECT b1.id
                  FROM stage.cats as b1
                  ORDER BY b1.geom <-> l.geom 
                  LIMIT 1) bird_id
    FROM stage.users As l1
) l
LEFT JOIN stage.dogs as g ON g.id = l.dog_id
LEFT JOIN stage.dogs as c ON c.id = l.cat_id
LEFT JOIN stage.dogs as b ON b.id = l.bird_id;

SQL Error subquery must return only one column

I suspect that you want an update statement rather than insert, because you are mentionning that youw ant the values to be changed. That's what an update does, while an insert creates new rows.

The logic you are looking for could be:

update v1
set 
    newstart = case 
        when starting - lag >= 7 then starting - 7
        else lag + 1
    end,
    newend = case 
        when starting - lag >= 7 then starting - 8
        else lag
    end

There is no need for a do block to implement this logic, a straight query is sufficient.

error: subquery must return only one column during insert

Use insert . . . select`:

INSERT INTO some_table (id, value1, value2, value3)
    SELECT ut.id, et.value1, et.value2, <value3>
    FROM user_table ut JOIN
         event_table et
         ON ut.some_column = 'data' AND et.type = 'some_type';

Notes:

• It is not clear where value3 comes from. I assume it is a constant of some sort.
• This version will not return any rows if the conditions do not match in either table. I assume this is a feature and not a bug.

subquery must return only one column error occurs when trying to run sql script

I would suggest removing the FILTER entirely and you want a correlation clause instead of a JOIN:

update "user" u
    set open_jobs = (SELECT COUNT(*) AS open_cnt
                     FROM job_card jc 
                     WHERE u."_id_" = jc.technicians_fk AND
                           jc.status IN ('Pending', 'Work Started')
                    );

The WHERE clause may be more efficient. If there are no matches, the COUNT(*) returns 0.

Your version of the subquery is almost what you want . . . if you move it to a FROM clause:

UPDATE "user" u
    SET open_jobs = COALESCE(jc.cnt)
    FROM (SELECT u."_id_", COUNT(*) as cnt FILTER (WHERE jc.status IN ('Pending', 'Work Started')) AS open_cnt
          FROM "user" u LEFT JOIN
               job_card jc
               ON u."_id_" = jc.technicians_fk
          GROUP BY u."_id_"
         ) jc
    WHERE jc."_id_" = u."_id_";

One important difference is that "user" is the first table for the LEFT JOIN, to ensure that you get all users.

SQLSTATE[42601]: Syntax error: 7 ERROR: subquery must return only one column Using Function

Because the question has an incorrect answer, I'm providing an answer beyond the comment. The code that you want is:

curProd := (
    SELECT "KeysForSale"
    FROM "KeysForSale"
    WHERE row_STab.product_id = "KeysForSale".product_id AND (("KeysForSale".begin_date < payment_date AND "KeysForSale".end_date > payment_date) OR ("KeysForSale".discounted_price IS NULL))
    ORDER BY "KeysForSale".discounted_price ASC NULLS LAST
    LIMIT 1
);

The difference is the lack of .*. Your version is returning a bunch of columns -- which is the error you are getting. You want to return a single record. The table name provides this.

I also think that parentheses will have the same effect:

    SELECT ("KeysForSale".*)

subquery must return only one column during insert data for returning

You can use a CTE with returning . . . and then a query:

with i as (
      insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)
      values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)
      returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id)
select i.*,
       (select p.name as province from provinces as p where p.id = i.province_id),
       (select c.name  as city from cities as c where c.id = i.city_id),
       (select d.name as district from districts as d where d.id = i.district_id),
       (select v.name as village, v.postal from villages as v where v.id = i.village_id)    
from i;

However, the above is returning multiple rows for villages. Probably the best way to fix that is with proper joins:

with i as (
      insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)
      values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)
      returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id)
select i.*, p.name as province, c.name as city, d.name as district,
       v.name as village, v.postal   
from i left join
     province p
     on p.id = i.province_id left join
     cities c 
     on c.id = i.city_id left join
     districts d 
     on d.id = i.district_id left join
     villages v 
     on v.id = i.village_id

---

Related Topics