Drizzle Team•5mo ago

Issue with SUM in sql template

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

This query works fine if the rows are available an produce the correct result. But for conditions that the rows are 0, boPending and notLocked result in null I tried

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

but the result is same. Is there any issue related to this that I can follow? Are there any alteranitives using query builer instead of sql template?

1 Reply

Angelelz•5mo ago

There is no such issue to my knowledge. Can you run the raw query and produces your expected results?

Gaming

Programming

Issue with SUM in sql template