sql - Summarizing query results

Question

Looking to summary the information from this query.

SELECT 
    SUM(DISTINCT CASE WHEN BatchNumber < '100' THEN 1 ELSE 0 END) AS A,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '22%' THEN 1 ELSE 0 END) AS B,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '33%' THEN 1 ELSE 0 END) AS C,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '44%' THEN 1 ELSE 0 END) AS D,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '55%' THEN 1 ELSE 0 END) AS E,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '66%' THEN 1 ELSE 0 END) AS F,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '77%' THEN 1 ELSE 0 END) AS G,
    SUM(DISTINCT CASE WHEN BatchNumber LIKE '88%' THEN 1 ELSE 0 END) AS H
FROM 
    Database.dbo.[Transaction]
WHERE 
    EffectiveDateTime = '2012-01-03'
GROUP BY 
    DepositBatchNumber

I get this (image is a partial of the results):

I am looking to get this:

If I use COUNT (without the distinct option) instead of SUM, I get the actual count of the data in the table which I do not want.

SELECT 
    COUNT(CASE WHEN BatchNumber < '100' THEN 1 END) AS A,
    COUNT(CASE WHEN BatchNumber LIKE '22%' THEN 1 END) AS B,...

Thanks in advance.

Answer 1

EDIT: I think I've got it now: You are looking for the number of distinct DepositBatchNumbers!

SELECT 
    count(distinct case when BatchNumber < '100' then DepositBatchNumber end) as A,
    count(distinct case when BatchNumber like '22%' then DepositBatchNumber end) as B,
    ...
FROM Database.dbo.[Transaction]
WHERE EffectiveDateTime = '2012-01-03';