如何合并单表同字段多条件COUNT查询？SQL自连接方案问询

Hey there! Let's figure out how to merge those two monthly activity count queries into one using self-joins and COUNT, just like you asked. I'll also throw in a more efficient alternative as a bonus—might come in handy for larger datasets!

First, Let's Recap Your Original Queries #

Just to confirm we're aligned, here are your two original queries (I've filled in the missing logic for the second one):

-- Query 1: Count monthly CALLs per user
SELECT completed_by_id AS WHO, COUNT(activity_id) AS CALLS
FROM table1
WHERE activity_id = 'CALL' 
  AND YEAR(completed_date) = YEAR(GETDATE()) 
  AND MONTH(completed_date) = MONTH(GETDATE())
GROUP BY completed_by_id;

-- Query 2: Count monthly VISITs per user
SELECT completed_by_id AS WHO, COUNT(activity_id) AS VISITS
FROM table1
WHERE activity_id = 'VISIT' 
  AND YEAR(completed_date) = YEAR(GETDATE()) 
  AND MONTH(completed_date) = MONTH(GETDATE())
GROUP BY completed_by_id;

Solution Using Self-Join + COUNT #

We can wrap each original query as a subquery, then join them on the completed_by_id (your WHO field) to combine their results. A FULL OUTER JOIN ensures we don't miss users who only have CALLs or only have VISITs, and we'll use ISNULL() to replace any NULL counts with 0 for cleaner output.

SELECT 
    COALESCE(call_data.WHO, visit_data.WHO) AS WHO,
    ISNULL(call_data.CALLS, 0) AS CALLS,
    ISNULL(visit_data.VISITS, 0) AS VISITS
FROM (
    -- Subquery for CALL counts
    SELECT completed_by_id AS WHO, COUNT(activity_id) AS CALLS
    FROM table1
    WHERE activity_id = 'CALL' 
      AND YEAR(completed_date) = YEAR(GETDATE()) 
      AND MONTH(completed_date) = MONTH(GETDATE())
    GROUP BY completed_by_id
) call_data
FULL OUTER JOIN (
    -- Subquery for VISIT counts
    SELECT completed_by_id AS WHO, COUNT(activity_id) AS VISITS
    FROM table1
    WHERE activity_id = 'VISIT' 
      AND YEAR(completed_date) = YEAR(GETDATE()) 
      AND MONTH(completed_date) = MONTH(GETDATE())
    GROUP BY completed_by_id
) visit_data ON call_data.WHO = visit_data.WHO;

Quick Notes on This Approach: #

• FULL OUTER JOIN: If you’re certain every user has both CALL and VISIT records, swap this for an INNER JOIN to simplify. But FULL OUTER JOIN is safer if some users only have one type of activity.
• COALESCE: Picks the first non-null value for the WHO column, so we never get a NULL user ID even if someone only exists in one subquery.
• ISNULL: Converts missing counts (like a user with no VISITs) from NULL to 0, which is more readable for reporting.

A More Efficient Alternative: Conditional Aggregation #

While you asked for a self-join solution, I wanted to share a better-performing option that only scans the table once instead of twice. It uses conditional aggregation with CASE statements inside the COUNT function:

SELECT 
    completed_by_id AS WHO,
    COUNT(CASE WHEN activity_id = 'CALL' THEN activity_id END) AS CALLS,
    COUNT(CASE WHEN activity_id = 'VISIT' THEN activity_id END) AS VISITS
FROM table1
WHERE YEAR(completed_date) = YEAR(GETDATE()) 
  AND MONTH(completed_date) = MONTH(GETDATE())
  AND activity_id IN ('CALL', 'VISIT') -- Filter to only relevant activities
GROUP BY completed_by_id;

Why This Works: #

• The CASE statement returns the activity_id only when the condition matches; otherwise, it returns NULL.
• COUNT() ignores NULL values, so it only counts rows that match each activity type.
• This is way more efficient for large tables since it’s a single pass over the data.

内容的提问来源于stack exchange，提问作者sstiebinger