I have a select query inside a select query which throws the error :"more than one row returned by a subquery used as an expression"

Question

I am trying to display multiple things such as userid, salary, etc. I used a select statement to do this. In this select statement, i need to use another select statement to provide 'fraction of salaries' . The query of fraction is correct. When i try to use this select into the main select, it gives the error "more than one row returned by.." Sample data:

//sample data
insert into employee(id_user int,salary_date date,salary_value) values 
(3, 2017-06-28, 15)
( 5, 2017-06-26 5)
( 2, 2017-06-20 5)
( 1, 2017-06-20 15)
( 4, 2017-06-17 25)

//expected output:
id date       sal  newdate   days aboveavgsal     date     current/firstsal 
1  2017-06-20 15  2017-05-20   31   N           2017-04-20    0.6
1  2017-05-20 10  2017-04-20   30   N           2017-04-20    0.4
1  2017-04-20 20  2017-03-20   31   Y           2017-03-20    0.8
1  2017-03-20 20  2017-02-20   28   Y           NULL          0.8
1  2017-02-20 15  NULL         NULL N           NULL          0.6

    SELECT  id_user
            , salary_date
            , salary_value
            , lead(salary_date) OVER(PARTITION BY id_user ORDER BY salary_date desc)
            , salary_date::timestamp - lead(salary_date::timestamp) over (PARTITION BY id_user ORDER BY salary_date DESC)
            , CASE WHEN salary_value >= 20 THEN 'Y' ELSE 'N' END
            , CASE WHEN salary_value >= 20 THEN lead(salary_date) OVER(PARTITION BY id_user ORDER BY salary_date DESC) END
            , (SELECT CAST(t1.salary_value AS float) / CAST(t.salary_value AS float) 
               FROM (
                     SELECT  t.id_user
                             , t.salary_value
                             , ROW_NUMBER() OVER(PARTITION BY t.id_user ORDER BY t.salary_date) AS rowrank
                     FROM employee t
                    ) AS t
                    INNER JOIN employee AS t1 ON t1.id_user = t.id_user
                    WHERE t.rowrank = 1
                    GROUP BY t1.id_user, t1.salary_value, t1.salary_date, t.salary_value, t.rowrank
                    ORDER BY t1.id_user, t1.salary_date DESC
              ) AS fraction
    FROM employee


Error starts in this query which i tried enclosing in braces.

    (SELECT  CAST(t1.salary_value AS float) / CAST(t.salary_value AS float) 
     FROM (
           SELECT t.id_user
                  , t.salary_value
                  , row_number() OVER(PARTITION BY t.id_user ORDER BY t.salary_date ) AS rowrank
           FROM employee t) AS t
           INNER JOIN employee t1 ON t1.id_user = t.id_user
           WHERE t.rowrank = 1
           GROUP BY t1.id_user, t1.salary_value, t1.salary_date, t.salary_value, t.rowrank
           ORDER BY t1.id_user, t1.salary_date DESC
          )

Try to debug by running the subquery independantly and see if it returns more than one row. — Michael Muryn, Mar 27 '19 at 18:48
Also it doesn't seem to have any condition linking the main query table to that subquery to isolate one row linked to that main query... so something must be missing there. And chance are you are looking to put that subquery linked to the main query instead of using a subquery. — Michael Muryn, Mar 27 '19 at 18:49
The subquery returns more than one row, so it cannot appear in the `SELECT` list. Maybe you can put it in the `FROM` clause? — Laurenz Albe, Mar 27 '19 at 18:49
And this is a bit subjective, but for such more complex query, I would be very inclined to write it a more clear way, everything is packed together. I have done so locally just to understand what is going on. I can suggest a way to write this after the other questions have been cleared up. — Michael Muryn, Mar 27 '19 at 18:51
@MichaelMuryn Yes, i ran the query separately. It works fine. It returns just one columns with the same rows as the other select query. — Gourishankar Bawade, Mar 27 '19 at 18:55
@PavelSmirnov It only contains int salary, int user id, date date; 68 3 2017-06-28 15; 67 5 2017-06-26 5; 66 2 2017-06-20 5; — Gourishankar Bawade, Mar 27 '19 at 19:01
The subquery is supposed to return more than one row.. I want those rows along with the results of other rows. The no of rows are same. — Gourishankar Bawade, Mar 27 '19 at 19:03
Then you need to move that subquery probably as a JOIN to the main query. — Michael Muryn, Mar 27 '19 at 19:05
@LaurenzAlbe yes it does return more than one row. So it cant appear in select list? So how to do it then? From clause? — Gourishankar Bawade, Mar 27 '19 at 19:05
I would suggest to provide a sample data in the form of INSERT INTO TABLE VALUES... Then provide a sample of expected results. That will help us help you. :) — Michael Muryn, Mar 27 '19 at 19:05
Why do this INNER JOIN employee AS t1 ON t1.id_user = t.id_user ... are not there is ONLY ONE employee.id_user in your table (e.g. id is unique, or there is multiple employee entry per user id?) — Michael Muryn, Mar 27 '19 at 19:06
@MichaelMuryn I thought of join.. but the subquery already has 2 joins. I'm not to figure that out — Gourishankar Bawade, Mar 27 '19 at 19:07
@MichaelMuryn there are multiple entries per employee for salary. In that particular query, I am merging the two queries to find the fraction of (current sal/ First sal) , So there are multiple salary changes in employees. There are 5 emmployees with multiple salary changes — Gourishankar Bawade, Mar 27 '19 at 19:10
Possible duplicate of [Postgres Error: More than one row returned by a subquery used as an expression](https://stackoverflow.com/questions/21048955/postgres-error-more-than-one-row-returned-by-a-subquery-used-as-an-expression) and ["subquery returns more than 1 row" error](https://stackoverflow.com/q/7658650/62576) — Ken White, Mar 27 '19 at 20:59
If I understand right by looking fast, that subquery idea is to get the first salary of the user, right? To do that famous calculation current_salary / first_salary, right? — Michael Muryn, Mar 28 '19 at 01:00
Have you validated your example data or they are gibberish display. For example, the first entry for salary (lowest date), I would expect it to have a ratio of 1 since it will divide by itself. — Michael Muryn, Mar 28 '19 at 01:03
Can you add an id column in that table to easily identify each row by one field? — Michael Muryn, Mar 28 '19 at 01:04

score 0 · Answer 1 · answered Mar 27 '19 at 19:23

0

In this part of your statement

(SELECT  CAST(t1.salary_value AS float) / CAST(t.salary_value AS float)

The cast requires t1.salary to be a single value.

However this part is returning more than one row.

       SELECT t.id_user
              , t.salary_value
              , row_number() OVER(PARTITION BY t.id_user ORDER BY t.salary_date ) AS rowrank
       FROM employee t) AS t
       INNER JOIN employee t1 ON t1.id_user = t.id_user
       WHERE t.rowrank = 1
       GROUP BY t1.id_user, t1.salary_value, t1.salary_date, t.salary_value, t.rowrank
       ORDER BY t1.id_user, t1.salary_date DESC

answered Mar 27 '19 at 19:23

cbeckley

11
4

I want it to return more than one row..many rows infact. I will have to use another join, is it? – Gourishankar Bawade Mar 27 '19 at 19:31
Ah, yes, in that case you need to take that sub-select and turn it into an inline table and join it in, and then perform your calculations. – cbeckley Mar 27 '19 at 19:43
But if i make a table and join it, it affects my previous queries in their groups and order by and much more – Gourishankar Bawade Mar 27 '19 at 19:59
yes, I'm afraid there's no way around that. – cbeckley Mar 27 '19 at 20:03
1) Say, i joined the two queries, which no of rows increase significantly? 2) Does it matter if the first query returns 4 rows and the other query which i m going to join returns 6, does the difference matters? – Gourishankar Bawade Mar 27 '19 at 20:06

I have a select query inside a select query which throws the error :"more than one row returned by a subquery used as an expression"

1 Answers1