# JuliaDBMeta/JuliaDB - How to select columns dynamically/programatically

**URL:** <https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082>\
**Category:** General Usage\
**Tags:** juliadb\
**Created:** [July 6, 2019, 11:45pm UTC](https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082 "2019-07-06T23:45:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [July 6, 2019, 11:45pm UTC](https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082/1 "2019-07-06T23:45:41Z")

</div>

I’ve been beating my head against the wall for hours on end trying to figure out how to avoid hard-coding column names in the below example (specifically, in the array comprehension `wgts = [sum(cols(Symbol("PWGTP$i"))) for i in 1:80]`).

```julia
function test(state::Int64, occ::Int64)
       @applychunked tbl begin
            @where !ismissing(:OCCP) &&
            :ST == state &&
            (:ESR == 1 || :ESR == 2) &&
            :OCCP == occ
            @groupby _ :PUMA { total = sum(:PWGTP), wgts = [sum(cols(Symbol("PWGTP$i"))) for i in 1:80] }
        end
    end

```

I get an error when doing this: `UndefVarError: i not defined`. However, it doesn’t appear that you can select columns by their index (at least not in this context) so I don’t know how I can perform operations such as the above one without having to write an insanely long line of code with all 80 variables.

In the data, there are columns `:PWGTP, :PWGTP1, ... ,:PWGTP80` and I need to be able to sum the values in columns `:PWGTP1, ... , :PWGTP80`, kind of like I’ve done with the `total = sum(:PWGTP)` piece, just without having to write sum() for all 80 columns…

I’m hoping someone familiar with JuliaDB/JuliaDBMeta can help.

Thanks!

---

<div class="post-metadata">

**Author:** ![MaximilianJHuber](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/maximilianjhuber/32/2579_2.png) [@MaximilianJHuber](https://discourse.julialang.org/u/MaximilianJHuber)\
**Post date:** [July 8, 2019, 10:19pm UTC](https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082/2 "2019-07-08T22:19:06Z")

</div>

I would first sum up the columns:

```julia
syms = [Symbol("PWGTP$(i)") for i in 1:80]
tbl = @transform tbl {PWGTP_sum = sum([getfield(_, s) for s in syms])}

```

and then sum up over rows in your `@groupby`. @piever certainly knows some syntax sugar for my solution above!

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [July 9, 2019, 10:20am UTC](https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082/3 "2019-07-09T10:20:40Z")

</div>

One trick is to use `_` inside the macro to access the whole table (or the whole row in a “row-based” macro). For example

```julia
function test(state::Int64, occ::Int64)
       @applychunked tbl begin
            @where !ismissing(:OCCP) &&
            :ST == state &&
            (:ESR == 1 || :ESR == 2) &&
            :OCCP == occ
            @groupby :PUMA { total = sum(:PWGTP), wgts = [sum(column(_, Symbol("PWGTP$i"))) for i in 1:80] }
        end
    end

```

`cols` tries to somehow also discover the column identities statically for extra optimizations so it doesn’t work inside a for loop, but at least the error message could be better. Would you mind opening an issue about this?

In this particular case you can also use JuliaDB special selectors (in this case a Regex) to get all columns that start with `PWGTP` (special selectors are documented at [https://juliacomputing.github.io/JuliaDB.jl/latest/basics/#Selectors-1](https://juliacomputing.github.io/JuliaDB.jl/latest/basics/#Selectors-1))

```julia
@groupby :PUMA { total = sum(:PWGTP), wgts = [sum(col) for col in columns(_, r"^PWGTP")] }

```

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [July 9, 2019, 11:19am UTC](https://discourse.julialang.org/t/juliadbmeta-juliadb-how-to-select-columns-dynamically-programatically/26082/4 "2019-07-09T11:19:52Z")

</div>

@MaximilianJHuber @piever Thank you so much! I opened the issue as requested. 👍 😃

I think it would also be good to add this `column()` function to the (JuliaDBMeta?) documentation as I don’t see it anywhere. I found `cols()` obviously and I see that JuliaDB has a `columns()` function but I was unaware of `column()`.