Select statement with additional columns

Community Support
1

I have a table with many columns and so far I’ve been doing:
select col1,col2,col3,…,col100,sum:col1+col2 from mytable

Is there an easy way to do :

select *, sum as (col1+col2) from table

I looked at functional queries ( http://www.timestored.com/kdb-guides/functional-queries-dynamic-sql) but that’s a little bit over my head. Is there an easier way to do this?


Thanks

2

Functional select is the best approach, but failing that you could just use update

update newcol:col1+col2 from tab

and it will return all columns. If you wanted to exclude certain columns you could follow that up with a delete

delete col4,col5 from update newcol:col1+col2 from tab

But again, getting your head around functional is better in the long term.

Terry

3

Using functional select:

q)a

a b c

2 m 1

3 i 2

4 k 3

q)c:(cols a)!cols a

q)c:c,(enlist d)!enlist (sum;(enlist ;a;`c))

q)?[`a;();0b;c]

a b c d

2 m 1 3

3 i 2 5

4 k 3 7

But if you want all columns + new column update will work as Terry posted.

Regards,

Showvik

4

Looks good.<o:p></o:p>

Thanks.<o:p></o:p>

<o:p> </o:p>

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of showvik
Sent: Tuesday, February 10, 2015 11:40 AM
To: personal-kdbplus@googlegroups.com
Subject: Re: [personal kdb+] Select statement with additional columns<o:p></o:p>

<o:p> </o:p>

Using functional select:<o:p></o:p>

<o:p> </o:p>

q)a<o:p></o:p>

a b c<o:p></o:p>

-----<o:p></o:p>

2 m 1<o:p></o:p>

3 i 2<o:p></o:p>

4 k 3<o:p></o:p>

q)c:(cols a)!cols a<o:p></o:p>

q)c:c,(enlist d)!enlist (sum;(enlist ;a;`c))<o:p></o:p>

q)?[`a;();0b;c]<o:p></o:p>

a b c d<o:p></o:p>

-------<o:p></o:p>

2 m 1 3<o:p></o:p>

3 i 2 5<o:p></o:p>

4 k 3 7<o:p></o:p>

<o:p> </o:p>

But if you want all columns + new column update will work as Terry posted.<o:p></o:p>

<o:p> </o:p>

Regards,<o:p></o:p>

Showvik<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

On Tue, Feb 10, 2015 at 9:58 PM, Terry Lynch <tlyncher@gmail.com> wrote:<o:p></o:p>

Functional select is the best approach, but failing that you could just use update<o:p></o:p>

update newcol:col1+col2 from tab<o:p></o:p>

and it will return all columns. If you wanted to exclude certain columns you could follow that up with a delete<o:p></o:p>

delete col4,col5 from update newcol:col1+col2 from tab<o:p></o:p>

But again, getting your head around functional is better in the long term. <o:p></o:p>

Terry<o:p></o:p>

On Feb 10, 2015 11:22 AM, “Bardiya Choupani” <bardiya@gmail.com> wrote:<o:p></o:p>

I have a table with many columns and so far I’ve been doing:<o:p></o:p>

select col1,col2,col3,…,col100,sum:col1+col2 from mytable<o:p></o:p>

Is there an easy way to do :<o:p></o:p>

select *, sum as (col1+col2) from table <o:p></o:p>

I looked at functional queries ( http://www.timestored.com/kdb-guides/functional-queries-dynamic-sql) but that’s a little bit over my head. Is there an easier way to do this?<o:p></o:p>

<o:p> </o:p>

Thanks<o:p></o:p>

–
Submitted via Google Groups