CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
jskattt797
jskattt797

Reputation: 271

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:

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.

Upvotes: 3

Views: 3473

Answers (2)

David Makogon
David Makogon

Reputation: 71022

Instead of doing counts of the overall query, you can use GROUP BY to get counts in a single query. For example:

SELECT c.time, c.status, COUNT(c.status) AS statuscount
FROM c
WHERE c.time = "1623332779"
GROUP BY c.time, c.status

This won't give you explicit counts called "successes" and "failures" but it will return both counts, something like:

[
  {
    "time": "1623332779",
    "status": "FAILURE",
    "statuscount": 123
  },
  {
    "time": "1623332779",
    "status": "SUCCESS",
    "statuscount": 456
  }
]

Upvotes: 3

NotFound
NotFound

Reputation: 6112

The following should work. The count operator skips values that are undefined which allows you to filter out rows from it:

SELECT 
    COUNT(c.status = 'SUCCESS' ? 1 : undefined) AS successes, 
    COUNT(c.status = 'FAILURE' ? 1 : undefined) AS failures
FROM c
WHERE c.time = 1623332779

It ruins performance though as it doesn't use indexing at all for the count. So you're better off using two seperate queries. If you really want to use a single request you could create a stored procedure that runs both queries and pastes the results together.

Upvotes: 3

Related Questions