SQL Counting values in a column

user_14986406 0
Viewed 50

Fairly new to SQL and need a quick answer. I have been looking for most of today and need a quick answer so I apologize if this is fairly basic. I am looking for a way to 'summarize' the columns from this query.

SELECT 
    Count(case when [BatchNumber] < '100' then 1 else 0 end) as A,
    Count(case when [BatchNumber] like '22%' then 1 else 0 end) as B,
    Count(case when [BatchNumber] like '33%' then 1 else 0 end) as C,
FROM [Database].[dbo].[Transaction]
WHERE [Date] between '2012-01-03' and '2012-01-05'
Group by [Date]

Data in the column is similar to:

Date             Batchnumber
2012-01-03       1               
2012-01-03       2                
2012-01-03       3              
2012-01-03       4               
2012-01-03       2201
2012-01-03       2202
2012-01-03       3301
2012-01-03       3302
2012-01-03       3303
2012-01-05       1
2012-01-05       2
2012-01-05       3
2012-01-05       4
2012-01-05       5
2012-01-05       3301
2012-01-05       3302
2012-01-05       3303
2012-01-05       3304

Looking for output something a long the lines of:

Date            A          B          c
2012-01-03      4          2          3
2012-01-05      5          0          4

Thank you in advance for any help.

EDIT: Rolling this back as I did not present my initial problem correctly. The info provided does answer the initial post.

sql-server sql
2 Answers

I don't think you want the distinct, and you need a GROUP BY:

SELECT Date,
       sum(case when [BatchNumber] < '100' then 1 else 0 end) as A,
       sum(case when [BatchNumber] like '22%' then 1 else 0 end) as B,
       sum(case when [BatchNumber] like '33%' then 1 else 0 end) as C,
FROM [Database].[dbo].[Transaction]
WHERE [Date] between '2012-01-03' and '12-01-05'
GROUP BY Date;

The case expressions returns either 0 or 1, so the maximum distinct values are 2.

user_11440350

try something like this, counting the columns and grouping by date

SELECT
        [date],
        sum(case when [BatchNumber] < '100' then 1 else 0 end) as A,
        sum(case when [BatchNumber] like '22%' then 1 else 0 end) as B,
        sum(case when [BatchNumber] like '33%' then 1 else 0 end) as C
    FROM [Database].[dbo].[Transaction]
    WHERE [Date] between '2012-01-03' and '2012-01-05'
    GROUP BY [Date]
    ORDER BY [Date]
user_29775440
Related