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;
ERROR: Subquery must return only one column
Moderator: NorbertKrupa
-
- Newbie
- Posts: 7
- Joined: Wed Oct 26, 2016 5:12 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: ERROR: Subquery must return only one column
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:
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...
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 7
- Joined: Wed Oct 26, 2016 5:12 pm
Re: ERROR: Subquery must return only one column
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
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
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: ERROR: Subquery must return only one column
Hi,
Do you really need to use DISTINCT? Maybe a simple GROUP BY will suffice?
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 7
- Joined: Wed Oct 26, 2016 5:12 pm
Re: ERROR: Subquery must return only one column
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 |
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 |