Add a "Sum" Row to a table

Question

I have a query that creates a table splitting out sales data by department (tops, bottoms, jewelry, etc). I then set a date period and sum the total sales, items and costs for each department as individual columns.

I'd now like to include a "Total" row to the table. I thought this could be handled by using insert:

`SalesByDept insert (enlist `total;sum(QTY);sum(Sales);sum(Cost))

This doesn't work but the strange thing is I can't even just insert a total row by doing

`SalesByDept insert (enlist `total;1;1;1)

Error: 'type

Anybody know where I'm getting tripped up?

nyi · Accepted Answer · 2018-07-28 21:08:47Z

4

This type error here is telling you one of the values you are trying to insert is not conforming with the column it is being inserted into.

I imagine it's the cost column, it's likely a float in the table. Either way you can find out the bad column[s] by something like this:

q)t:([]dep:();qty:();sales:();cost:()); `t insert (10?`1;10?10;10?1000;10?1000.);
q)t2:select sum qty, sum sales, sum cost by dep from t; newrow:(`total;1;1;1)
q)// what columns are matching
q)exec c where type'[newrow]<>neg .Q.t?t from meta t2
,`cost
q)// what's the diffs
q)// t2 cost type
q)meta[t2][`cost;`t]
"f"
q)// newrow cost type
q).Q.t abs type newrow cols[t2]?`cost
"j"
q)// change to expected type (leading dot makes it a float type) and see if insert works
q)newrow:(`total;1;1;1.)
q)// works now
q)`t2 insert newrow
,8

An alternative way to insert a 'totals' row, without having to worry about type:

`t2 upsert (enlist[`dep]!enlist `total),last sums t2

HTH, Sean

edited Jul 28, 2018 at 21:08

nyi

3,2494 gold badges25 silver badges47 bronze badges

answered Jul 28, 2018 at 20:34

Sean O'Hagan

1,7078 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ks-man Over a year ago

Thanks a lot! I'm still digesting your top part but the bottom upsert definitely takes care of what I was looking for.

Collectives™ on Stack Overflow

Add a "Sum" Row to a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related