SQL查询优化：用子查询统计替代AND过滤，一次获取多维度数据

You can modify your query to include the non-billable document count in a single pass by using a conditional aggregate (a CASE statement wrapped in an aggregate function like SUM or COUNT). This lets you calculate both totals without re-running the query.

Here's the updated query:

select 
    du.USERID as 'Username', 
    du.LIBRARYNAME as 'Home Office', 
    count(*) as 'Documents created',
    SUM(CASE WHEN dm.C1ALIAS like '9999%' THEN 1 ELSE 0 END) as 'Non-Billable Docs'
from mhgroup.dochistory dh (nolock) 
inner join mhgroup.DOCMASTER dm (nolock) on (dh.DOCNUM=dm.DOCNUM and dh.VERSION=dm.VERSION) 
inner join mhgroup.DOCUSERS du (nolock) on dh.DOCUSER=du.USERID 
where (dh.activity_datetime >= dateadd(dd,-365, getutcdate())) 
    and du.LIBRARYNAME in ('DEN','HOU') 
    and dm.TYPE = 'd' 
    and dh.ACTIVITY = 'Create' 
group by du.USERID, du.LIBRARYNAME 
order by count(*) desc

How This Works: #

• The original count(*) still gives you the total number of created documents per user/home office.
• The SUM(CASE...) clause checks each row to see if dm.C1ALIAS starts with '9999'. For matching rows, it adds 1 to the sum; for non-matching rows, it adds 0. This gives you the exact count of non-billable documents per group.

Expected Result: #

Alternatively, you could use COUNT(CASE WHEN dm.C1ALIAS like '9999%' THEN 1 END) instead of SUM—this works because COUNT ignores NULL values (the CASE returns NULL when the condition isn't met, so those rows aren't counted). Both approaches will give you the same result.

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