Combining COUNT Queries

Question

I am counting specific things in a SQL Server table. I am using multiple count queries, but I am wondering if it's possible to combine them into a single query with the column name and count numbers in a single table for display.

My queries are:

select count(*) as Ask_Count
from Pld_OpenSalesOrdersLines
where left(C_Item_ID, 1) = '*'

select count(*) as M_Count
from Pld_OpenSalesOrdersLines
where (left(C_Item_ID, 1) = 'M' and len(C_Item_ID) = 1)

select count(*) as MN_Count
from Pld_OpenSalesOrdersLines
where (left(C_Item_ID, 2) = 'MN' and len(C_Item_ID) = 2)

I tried a couple stupid things to combine them, but they were a failure. I honestly can't even begin to think how to combine them, maybe it's not possible?

Thanks!

score 2 · Answer 1 · answered Dec 14 '18 at 19:58

You could use CASE expression to perform conditional aggregation:

select 
 COUNT(CASE WHEN LEFT(C_Item_ID,1)='*' THEN 1 END) AS Ask_Count,
 COUNT(CASE WHEN LEFT(C_Item_ID,1)='M' AND LEN(C_Item_ID)=1 THEN 1 END)  M_Count,
 COUNT(CASE WHEN LEFT(C_Item_ID,2)='MN' AND LEN(C_Item_ID)=2 THEN 1 END) MN_Count 
from Pld_OpenSalesOrdersLines

score 0 · Answer 2 · answered Dec 14 '18 at 20:00

Use conditional aggregation:

select sum(case when LEFT(C_Item_ID,1) = '*' then 1 else 0 end) as count_1, 
       sum(case when LEFT(C_Item_ID,1) = 'M' AND LEN(C_Item_ID)=1 then 1 else 0 end) as count_2,
       sum(case when LEFT(C_Item_ID,2) = 'MN' AND LEN(C_Item_ID)=2 then 1 else 0 end) as count_3
from Pld_OpenSalesOrdersLines;

I would write the logic like this, though:

select sum(case when C_Item_ID like '*%' then 1 else 0 end) as count_1, 
       sum(case when C_Item_ID = 'M' then 1 else 0 end) as count_2,
       sum(case when C_Item_ID = 'MN' then 1 else 0 end) as count_3
from Pld_OpenSalesOrdersLines;

Doing a left() on a column and then checking the length is redundant. Just use =.

Combining COUNT Queries

2 Answers2