Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 7 down vote accepted

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

SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid] 
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

Edit:

An aggregate without GROUP BY will always return a result (see SO here). I forgot that.

I've updated my SQL

share|improve this answer
    
yes, i guess the sent is a stick in the spokes... I was thinking more along the lines of what @Cape Cod Gunny posted. but im not sure if the multiple selects are better or worse that the full outer join you posted. thoughts? –  kacalapy May 6 '11 at 17:52
2  
@kacalapy: this is 2 queries of the table and one statement. The other answer is 6 queries and several statements. Even with indexing, it's 66% less work in simple terms –  gbn May 6 '11 at 18:06
    
@gbn - why not drop the joinuid stuff and use a cross join? –  Jack Douglas May 7 '11 at 7:27
    
@JackPDouglas: I did consider that. But if there are no sent message or no inbox messages you'd get no results. The MAX is only to avoid a GROUP BY (which would be useful for many uid values). (I've edited this comment BTW in case you read the previous) –  gbn May 7 '11 at 8:21
1  
@JackPDouglas: I assumed that there will be at least one message somwhere. As we know, assumption is the mother of all feckups :-). I did have 1=1 but removed for being too opaque. Ah well, OP has plenty of ideas on these comments now... Cheers –  gbn May 7 '11 at 8:47
DECLARE @Inbox  bigint
DECLARE @Unread bigint
DECLARE @Flagged bigint
/*
MORE DECLARE STATEMENTS GO HERE
*/

SET @Inbox   = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] 
SET @Unread  = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] and isread = 0
SET @Flagged = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] and isFlagged = 1

/*
MORE SELECT STATEMENTS GO HERE
*/

SELECT
@Inbox   as Inbox,
@Unread  as Unread,
@Flagged as Flagged

/*
MORE @Variables GO HERE
*/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.