CODEWITHSUNDEEP
sqlsqlitecount
aadu
aadu

Reputation: 3254

How to combine many COUNTs into one big query in SQLite?

I am using SQLite and performing many COUNT queries using a loop like so... 

foreach (var eventId in eventIds)
{
    Database.Query("SELECT COUNT(RowId) FROM Images WHERE EventId = " + eventId);
}

...each of these queries only takes something like 12ms but this adds up and can lead to 10 seconds of waiting. Is it possible to combine this into one query and an array of event ids?

Like...

SELECT COUNT(RowId) FROM Images WHERE EventId IN (10329, 10312, 10328, etc...)

... but obviously with individual COUNT's coming back?

Upvotes: 0

Views: 78

Answers (2)

Fahmi
Fahmi

Reputation: 37473

try this: this will give you count per event

SELECT EventId ,COUNT(RowId) FROM Images WHERE EventId IN (10329, 10312, 10328, etc...)
group by EventId

Upvotes: 3

sticky bit
sticky bit

Reputation: 37472

Use GROUP BY to get the count relative to an event.

SELECT eventid,
       count(*)
       FROM images
       WHERE eventid IN (4711,
                         747,
                         ...)
       GROUP BY eventid;

This will return something like:

eventid | count
========+======
   4711 |    31
    747 |    99
       ...

Upvotes: 4

Related Questions