Fork me on GitHub

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


@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.


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


@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?


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


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


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)


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


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)


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


@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 🙂


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