Search code examples
mysqlclojurekorma

Clojure + Korma - SUM aggregation query with IF condition

How does sum-if work in Korma?

Here is the sample query 

SELECT SUM(if(items.quantities > 1, 1, 0)) AS multiples FROM items;

I got this to work with raw-exec provided by Korma. But, I am interested in knowing how to write this in the Korma syntax.

I have tried looking at http://sqlkorma.com/docs#select

Solution

  • IF is a vendor specific function, so I doubt it will be supported.

    You might have better luck transforming your query into something vendor neutral and using that instead.

    This query:

    SELECT SUM(if(items.quantities > 1, 1, 0)) AS multiples FROM items;

    is equivalent to the following one:

    SELECT count(*) AS multiples FROM items WHERE quantities > 1;

    which translates to the following korma expression:

    (select items 
     (aggregate (count  :*) :multiples) 
     (where {:quantities [> 1]}))