Get multiple counts with one Cosmos DB query?


Author: jskattt797

Originally Sourced from: https://stackoverflow.com/questions/68478671/get-multiple-counts-with-one-cosmos-db-query

Consider these queries:

SELECT COUNT(1) AS failures 
FROM c 
WHERE c.time = 1623332779 AND c.status = 'FAILURE'
   
SELECT COUNT(1) AS successes 
FROM c 
WHERE c.time = 1623332779 AND c.status = 'SUCCESS'

How can I combine these two distinct queries into one query?

I tried repurposing the answers from How to get multiple counts with one SQL query?, but ran into a few problems:

  • COUNT(*) throws an error "Syntax error, incorrect syntax near '*'."
  • UNION throws "Syntax error, incorrect syntax near 'UNION'."

I also experimented with

SELECT 
    SUM(CASE WHEN c.time = 1623332779 THEN 1 else 0 end) 
FROM c

but this leads to another syntax error. I noticed that

SELECT COUNT(1) AS mycounter, COUNT(1) AS mycounter2 
FROM c 
WHERE c.time = 1623332779

returns

[
    {
        "mycounter": 3,
        "mycounter2": 3
    }
]

but I was unable to link these distinct counters to distinct queries.