Page 1 of 1
ERROR: Subquery must return only one column
Posted: Wed Oct 26, 2016 5:41 pm
by atul.pdm@gmail.com
I need to calculate the sum of wallclock and cpu based on the condition (slots > 1) from same table,
but I am getting erorr "Subquery must return only one column"
I tried below query:
select owner, job_number,slots,
(select sum(ru_wallclock) wallclock, sum(cpu) cpu from acctn where slots > 1)
from acctn;
Re: ERROR: Subquery must return only one column
Posted: Wed Oct 26, 2016 7:36 pm
by JimKnicely
Hi,
You are trying to return two columns (wallclock and cpu) in the sub-query in your column list for the over all query... You can't do that.
You have to split them up into two sub-queries.
Example:
Code: Select all
dbadmin=> create table acctn (owner varchar(10), job_number int, slots int, ru_wallclock int, cpu int);
CREATE TABLE
dbadmin=> insert into acctn values ('Jim', 1, 1, 1, 1);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into acctn values ('Jim', 1, 2, 1, 1);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into acctn values ('Jim', 1, 3, 1, 1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select owner, job_number,slots,
dbadmin-> (select sum(ru_wallclock) wallclock, sum(cpu) cpu from acctn where slots > 1)
dbadmin-> from acctn;
ERROR 4836: Subquery must return only one column
dbadmin=> select owner, job_number, slots,
dbadmin-> (select sum(ru_wallclock) from acctn where slots > 1) wallclock,
dbadmin-> (select sum(cpu) cpu from acctn where slots > 1) cpu
dbadmin-> from acctn;
owner | job_number | slots | wallclock | cpu
-------+------------+-------+-----------+-----
Jim | 1 | 1 | 2 | 2
Jim | 1 | 2 | 2 | 2
Jim | 1 | 3 | 2 | 2
(3 rows)
But are those the results you expect? You may want to group the data differently? That is, those sub-queries will SUM across the entire table, not just by owner and job_number...
Re: ERROR: Subquery must return only one column
Posted: Thu Nov 03, 2016 6:34 pm
by atul.pdm@gmail.com
Thanks for the reply.I need more help on this,
How to use the distinct function with case statement with multiple conditions?
Example:
I am running below query to calculate the sum of cpu and ru_wallclock on the condition that the job_number should be distinct but it is not working,please suggest
select owner,
sum(case
when distinct job_number and name!='A' and slots <= 1 then ru_wallclock/3600
when distinct job_number and name!='B' and slots > 1 then ru_wallclock*slots/3600
end) as ru_wallclock_new ,
sum(case
when distinct job_number and name!='A' and slots <= 1 then cpu/3600
when distinct job_number and name!='B' and slots <= 1 then cpu/3600
end ) as cpu_new
)
from acctn
group by owner
Re: ERROR: Subquery must return only one column
Posted: Fri Nov 04, 2016 3:42 pm
by JimKnicely
Hi,
Do you really need to use DISTINCT? Maybe a simple GROUP BY will suffice?
Code: Select all
dbadmin=> select * from acctn;
owner | job_number | slots | ru_wallclock | cpu | name
-------+------------+-------+--------------+-----+------
Jim | 1 | 1 | 1 | 1 | A
Jim | 1 | 2 | 1 | 1 | A
Jim | 1 | 3 | 1 | 1 | A
Jane | 1 | 1 | 1 | 1 | A
Jane | 1 | 1 | 1 | 1 | B
Jane | 2 | 1 | 1 | 1 | B
(6 rows)
dbadmin=> select owner,
dbadmin-> job_number,
dbadmin-> sum(case
dbadmin(> when name!='A' and slots <= 1 then ru_wallclock/3600
dbadmin(> when name!='B' and slots > 1 then ru_wallclock*slots/3600
dbadmin(> end) as ru_wallclock_new,
dbadmin-> sum(case
dbadmin(> when name!='A' and slots <= 1 then cpu/3600
dbadmin(> when name!='B' and slots <= 1 then cpu/3600
dbadmin(> end) as cpu_new
dbadmin-> from acctn
dbadmin-> group by owner, job_number;
owner | job_number | ru_wallclock_new | cpu_new
-------+------------+----------------------+----------------------
Jane | 1 | 0.000277777777777778 | 0.000555555555555556
Jane | 2 | 0.000277777777777778 | 0.000277777777777778
Jim | 1 | 0.001388888888888889 | 0.000277777777777778
(3 rows)
Re: ERROR: Subquery must return only one column
Posted: Mon Nov 07, 2016 12:30 pm
by atul.pdm@gmail.com
You are right but I need to calculate the sum of ru_wallclock and cpu for the one owner in single row but this output shows the multiple records for one owner.
Is there a way to calculate the sum of all the values in columns ru_wallclock and cpu for the particular owner? but before this the calculations should be done according to the distinct job_number and then it should calculate the sum of values in columns ru_wallclock and cpu for the single owner.
eg.
select owner,
sum(case
when distinct job_number and name!='A' and slots <= 1 then ru_wallclock/3600
when distinct job_number and name!='B' and slots > 1 then ru_wallclock*slots/3600
end) as ru_wallclock_new ,
sum(case
when distinct job_number and name!='A' and slots <= 1 then cpu/3600
when distinct job_number and name!='B' and slots <= 1 then cpu/3600
end ) as cpu_new
)
from acctn
group by owner
output:
owner | ru_wallclock | cpu |
-------+------------+-------+--------------+----
Jim | 3| | 3 |
Jane | 3| | 3 |