This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2017-03-27
Channels
- # bangalore-clj (1)
- # beginners (27)
- # boot (16)
- # cider (14)
- # cljs-dev (94)
- # cljsrn (8)
- # clojure (229)
- # clojure-dev (5)
- # clojure-dusseldorf (6)
- # clojure-italy (8)
- # clojure-norway (8)
- # clojure-russia (22)
- # clojure-sanfrancisco (2)
- # clojure-spec (48)
- # clojure-uk (44)
- # clojurescript (47)
- # core-async (87)
- # cursive (43)
- # datascript (22)
- # datomic (20)
- # defnpodcast (5)
- # emacs (6)
- # hoplon (4)
- # jobs-rus (4)
- # keechma (2)
- # klipse (8)
- # leiningen (2)
- # luminus (2)
- # lumo (14)
- # om (38)
- # onyx (4)
- # overtone (3)
- # pedestal (41)
- # planck (72)
- # powderkeg (42)
- # proton (46)
- # protorepl (9)
- # reagent (9)
- # ring (47)
- # ring-swagger (5)
- # rum (7)
- # sql (22)
- # unrepl (1)
- # untangled (24)
- # vim (19)
- # yada (5)
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
is it possible?
@josh.freckleton You can do COUNT(*) as y
in conjunction with GROUP BY y
. I think SUM
should work
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 x
s and y
s 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?
Something like SELECT x / y AS xy from (SELECT SUM(salary) AS x, COUNT(*) AS y FROM employees GROUP BY department)
Nested query syntax (and support) may vary by DB, YMMV
In the above query GROUP BY
would create multiple rows in the sub-select, so that might create multiple rows of xy
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?
You should probably run EXPLAIN
on the query to see the query plan (DB optimizer’s version of the execution)
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 EXPLAIN
ations 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!