Multiple COUNT columns in cosmos DB GROUP BY query

Question

I want to use multiple COUNT columns in cosmos DB GROUP BY query. I have tried with a query something like this.

SELECT c.departmentName,
CASE WHEN c.age > 50 THEN COUNT(c.EmpId) ELSE 0 END AS category1,
CASE WHEN c.age <= 50 THEN COUNT(c.EmpId) ELSE 0 END AS category2
FROM c
GROUP BY c.departmentName

But I'm getting error message:

"Syntax error, incorrect syntax near 'CASE'."

How can I apply multiple COUNT columns by filtering a property?

Answer 1

CosmosDB NoSQL API query syntax does not support CASE.

To write this in CosmosDB compatible syntax you could use the Ternary operator

SELECT c.departmentName,
       SUM(c.age <= 49 ? 1 : 0)  AS category1,
       SUM(c.age = 50  ? 1 : 0)  AS the_ignored,
       SUM(c.age >  50 ? 1 : 0)  AS category2
FROM c
GROUP BY c.departmentName

Answer 2

Maybe there is a more succinct way to do it, but I usually do something like this:

(if you want to count records even when c.EmpID is null, just omit that condition)

SELECT c.departmentName,
       sum(
       CASE WHEN c.age > 50 and c.EmpId is not null 
            THEN 1
        END) AS category1,
       sum(
       CASE WHEN c.age <= 49 and c.EmpId is not null 
            THEN 1
        END) AS category2
  FROM c
 GROUP 
    BY c.departmentName;

Edit:

If you want to do the count and have implicit handling of nulls

Fiddle: https://dbfiddle.uk/blL66rbz

create table c 
  (
    departmentName varchar(100),
    age integer,
    empID integer
  );

insert into c values 
  ('Accounting', 53, 1),
  ('Accounting', 48, 2),
  ('Accounting', 30, null),
  ('Accounting', 71, 6),
  ('IT', 25, 3),
  ('IT', 32, 4),
  ('IT', 51, 5);

select c.departmentName,
       count(case when c.age >= 50 then c.empID end) as count50orOver,
       count(case when c.age <= 49 then c.empID end) as countUnder50
  from c
 group
    by c.departmentName

Answer 3

Here's a common format for conditional aggregation. I don't have access to a Cosmos DB, but would imagine it works.

select 
 c.departmentName, 
 sum(case when c.age > 50 then 1 else 0 end) as category1, 
 sum(case when c.age <= 49 then 1 else 0 end) as category2
from table c
where c.age is not null 
group by c.departmentName