ERROR: Subquery must return only one column

Forum to discuss any topic generally related to SQL in Vertica

Moderator: NorbertKrupa

Post Reply
atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

ERROR: Subquery must return only one column

Post by atul.pdm@gmail.com » Wed Oct 26, 2016 5:41 pm

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;

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1741
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: Subquery must return only one column

Post by JimKnicely » Wed Oct 26, 2016 7:36 pm

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...
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

Re: ERROR: Subquery must return only one column

Post by atul.pdm@gmail.com » Thu Nov 03, 2016 6:34 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1741
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: Subquery must return only one column

Post by JimKnicely » Fri Nov 04, 2016 3:42 pm

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)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

Re: ERROR: Subquery must return only one column

Post by atul.pdm@gmail.com » Mon Nov 07, 2016 12:30 pm

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 |

Post Reply

Return to “Vertica SQL”

Who is online

Users browsing this forum: No registered users and 1 guest