Combining two counts with joins in one query

问题:

Ok - hoping someone can help as I'm struggling here. Have tried using sum(case when), count(*), subqueries with no success. Essentially I have two queries I am trying to combine into one, grouped by the same parameter. The queries:

Query 1:

SELECT
u.username,
count(*) as 'Total'

FROM log1 L1
JOIN users u on u.userref = L1.userref
WHERE L1.date between @FromDate and @ToDate
GROUP BY u.username
ORDER BY u.username

Query 2:

SELECT
u.username,
count(*) as 'Total'

FROM log2 L2
LEFT JOIN users u on u.userref = L2.userref
WHERE L2.date between @FromDate and @ToDate and L2.message like '%None%'
GROUP BY u.username
ORDER BY u.username

What I would like is a single query with a username column (u.username), a column showing the results of query 1, and a column showing the results of query two. Any help appreciated!

回答1:

You can put a case statement inside the count function to only count when certain criteria is met:

SELECT  u.username,
        Total = COUNT(*),
        Total2 = COUNT(CASE WHEN L.message LIKE '%None%' THEN 1 END)
FROM    log1 AS L
        JOIN users AS u 
            ON u.userref = L.userref
WHERE   L.date BETWEEN @FromDate and @ToDate
GROUP BY u.username
ORDER BY u.username;

Of note:

• BETWEEN can cause problems, especially when working with dates
• Using string literals for column aliases is on the deprecation list

EDIT

Completely missed that you had two separate log tables:

SELECT  u.username,
        Total = COUNT(Count1),
        Total2 = COUNT(Count2)
FROM    (   SELECT  l.UserRef, Count1 = 1, Count2 = NULL
            FROM    log1 AS L
            WHERE   L.date BETWEEN @FromDate and @ToDate
            UNION ALL
            SELECT  l.UserRef, Count1 = NULL, Count2 = 1
            FROM    log2 AS L
            WHERE   L.date BETWEEN @FromDate and @ToDate
            AND     L.message LIKE '%None%'
        ) AS l
        JOIN users AS u 
            ON u.userref = L.userref
GROUP BY u.username
ORDER BY u.username;

回答2:

           SELECT X.USERNAME,X.TOTAL,Y.TOTAL 
    FROM(SELECT
            u.username,
            count(*) as 'Total'
            FROM log1 L1
            JOIN users u on u.userref = L1.userref
            WHERE L1.date between @FromDate and @ToDate
            GROUP BY u.username

            ) as x
INNER JOIN
            (
            SELECT
            u.username,
            count(*) as 'Total'
            FROM log2 L2
            LEFT JOIN users u on u.userref = L2.userref
            WHERE L2.date between @FromDate and @ToDate and L2.message like '%None%'
            GROUP BY u.username
            ) AS Y ON Y.USERNAME = X.USERNAME