What is the best or most efficient way to get multiple aggregate result values?
Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, archived, flagged… ) as defined below.
Here is a sample of things I am trying to do in one stored proc call. (where [uid] is the userID)
SELECT * FROM Message
Inbox WHERE [to] = [uid]
Unread WHERE [to] = [uid] and isread = 0
Flagged WHERE [to] = [uid] and isFlagged = 1
Drafts WHERE [to] = [uid] and isDraft = 1
Sent Messages WHERE [from] = [uid]
Archived Messages WHERE [to] = [uid] and isArchived = 1
Best Answer
COUNT..CASE is the usual way. For "Sent" though it's bit trickier because it's a different uid filter which requires 2 queries on the Message table
I've used this construct to allow for zero row for to/from. It avoids an OR and adding a uid filter to each CASE.
Each derived table (can write as a CTE) will return only one row and I've used a MAX on uid to avoid GROUP BY too in case anyone wonders
Edit:
An aggregate without GROUP BY will always return a result (see SO here). I forgot that.
I've updated my SQL