Fork me on GitHub
#honeysql
<
2022-02-08
>
wcalderipe16:02:04

Hey, I wonder if is there a better way to write a query using CASE WHEN within the SUM? For example, getting rid of the :raw somehow.

{:select [[[:raw "SUM(CASE WHEN direction = 'in' THEN amount ELSE 0 END) as in"]]
          [[:raw "SUM(CASE WHEN direction = 'out' THEN amount ELSE 0 END) as out"]]]
 :from   [:foo]}

seancorfield16:02:27

@wcalderipe Why would you need :raw there?

dev=> (sql/format {:select [[[:sum [:case [:= :direction "in"] :amount :else 0]] :in]
 #_=>                       [[:sum [:case [:= :direction "out"] :amount :else 0]] :out]]
 #_=>              :from :foo})
["SELECT SUM(CASE WHEN direction = ? THEN amount ELSE ? END) AS in, SUM(CASE WHEN direction = ? THEN amount ELSE ? END) AS out FROM foo" "in" 0 "out" 0]

wcalderipe20:02:56

I wasn't sure if I need it; hence the question here. It's my first time using HoneySQL so I'm still learning how to work with it. Thanks for the answer 😄

1
seancorfield20:02:34

Use of :raw should be extremely rare -- and only needed for really unusual corner case syntax.

👌 1