SQL case statement summation and calc to dplyr

[Bill_Rowe]

I have as part of a SQL query the following as part of an aggregation that I want to convert to dplyr syntax:

case when sum(amy_jan) != 0 then sum(cost_jan)/sum(amy_jan) else 0 end as ratio_jan
...
case when sum(amy_dec) != 0 then sum(cost_dec)/sum(amy_dec) else 0 end as ratio_dec

What would that look like in dplyr syntax?

[nirgrahamuk]

(test1 <- data.frame(
  a=c(-1,1),
  b=c(2,2)
))
(test2 <- data.frame(
  a=c(1,1),
  b=c(2,2)
))
library(sqldf)

sqldf::sqldf("select 
             case when sum(a) != 0 then sum(b)/sum(a) else 0 end as c
             from test1")

sqldf::sqldf("select 
             case when sum(a) != 0 then sum(b)/sum(a) else 0 end as c
             from test2")

library(dplyr)

summarise(test1,
       c = case_when(sum(a) != 0 ~ sum(b)/sum(a) ,
                     TRUE ~ 0))

summarise(test2,
       c = case_when(sum(a) != 0 ~ sum(b)/sum(a) ,
                     TRUE ~ 0))