Fork me on GitHub
#sql
<
2017-03-27
>
lumi11:03:10

Hi, I’m having some trouble using sqlite with jdbc from clojure… I’m trying to feed a batch to a prepared statement, but I keep getting ArrayIndexOutOfBoundsException, from within the prepared statement

seancorfield16:03:14

@lumi If you can share the fragment of code, we are more likely to be able to help you, but I suspect you have the wrong number of parameters in your prepared statement.

josh.freckleton19:03:18

simple SQL question, I have a SUM(x) AS x and COUNT(y) as y, and want to return x / y AS xy

Shantanu Kumar19:03:15

@josh.freckleton You can do COUNT(*) as y in conjunction with GROUP BY y. I think SUM should work

Shantanu Kumar19:03:12

For x/y as xy you may need a nested query, but then you can write a stored proc to do this too

josh.freckleton19:03:18

@kumarshantanu thanks... I can return xs and ys just fine, but I can't do that arithmetic / on them. could you help me understand how a nested query would work?

josh.freckleton19:03:32

I would think that x/y AS xy should just... work... no?

Shantanu Kumar19:03:51

Something like SELECT x / y AS xy from (SELECT SUM(salary) AS x, COUNT(*) AS y FROM employees GROUP BY department)

Shantanu Kumar19:03:04

Nested query syntax (and support) may vary by DB, YMMV

Shantanu Kumar19:03:55

In the above query GROUP BY would create multiple rows in the sub-select, so that might create multiple rows of xy

Shantanu Kumar19:03:49

You may want to omit GROUP BY clause to just obtain a single number

josh.freckleton19:03:55

i'm testing these suggestions now, not sure that my setup allows nested sql...

josh.freckleton19:03:16

hm, this works

SUM(x) AS x,
COUNT(y) as y,
SUM(x) / COUNT(y) AS xy
but that seems inefficient, since the aggregates get calculated twice (and I'll need a lot more return columns based off of operating on aggregates)

josh.freckleton19:03:45

does sql optimize this in anyway, so that the aggregates aren't calculated multiple times?

Shantanu Kumar19:03:49

You should probably run EXPLAIN on the query to see the query plan (DB optimizer’s version of the execution)

Shantanu Kumar19:03:37

Something like EXPLAIN SELECT …, if your DB supports that

josh.freckleton19:03:56

im in mysql workbench, EXPLAIN works, not sure how to interpret though. It gives a "Query cost" that's the same for a query returning x, y, and x, y, xy (cost = 2056.20)

josh.freckleton19:03:45

it says it performs a full index scan on both... both EXPLAINations look the same...

josh.freckleton20:03:48

@kumarshantanu Thanks for your help, I made an SO question if you care to help me further and get SO street cred 😉 if not, I totally understand, you've already given me quite a bit of your time 🙂

josh.freckleton20:03:08

Solved, your answer was actually right and I had just implemented it wrong. Thank you again!