CODEWITHSUNDEEP
mysqlclojurekorma
Ratnadeep
Ratnadeep

Reputation: 133

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

Upvotes: 1

Views: 222

Answers (2)

chunfengd
chunfengd

Reputation: 61

I would use this one: 

(select items
        (fields (raw "SUM(if(items.quantities > 1, 1, 0)) AS multiples"))

Upvotes: 0

soulcheck
soulcheck

Reputation: 36767

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]}))

Upvotes: 1

Related Questions