SQL Counting values in a column

Question

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.

On a side note: Better change '12-01-05' to '2012-01-05' in order to be sure it gets interpreted correctly. — Thorsten Kettner
– Thorsten Kettner, Commented Jan 11, 2021 at 23:13
But now your query does not include [Date] in the column list while your "output" does. And note that if order of rows matters (and it usually does), your query MUST have an ORDER BY clause. — SMor
– SMor, Commented Jan 11, 2021 at 23:15
The now altered query with COUNT doesn't lack the GROUP BY anymore, but has the problem that both 0 and 1 are not null, so both get counted. COUNT(CASE WHEN ... THEN 1 ELSE 0 END) is hence the same as COUNT(*). You want SUM(CASE WHEN ... THEN 1 ELSE 0 END)instead or COUNT(CASE WHEN ... THEN 1 ELSE NULL END) or short COUNT(CASE WHEN ... THEN 1 END). — Thorsten Kettner
– Thorsten Kettner, Commented Jan 11, 2021 at 23:22

Gordon Linoff · Accepted Answer · 2021-01-11 23:21:44Z

2

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.

edited Jan 11, 2021 at 23:21

answered Jan 11, 2021 at 23:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian M Over a year ago

How does one do that count without the case? I have not been able to get a count without? If I take the case info out I get: count([BatchNumber] < '100' ) as A...which give an error in syntax.

Gordon Linoff Over a year ago

@BrianM . . . Use the case expression if you want conditional logic.

Thorsten Kettner · Accepted Answer · 2021-01-11 23:24:25Z

1

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]

edited Jan 11, 2021 at 23:24

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

answered Jan 11, 2021 at 23:06

The Harlequin

1882 silver badges11 bronze badges

2 Comments

Thorsten Kettner Over a year ago

GROUP BY [Date] is the correct solution. You should also show the date in the select list and order by it. COUNT(SUM(...)) however makes no sense. Just SUM(...) is correct. At last: WHERE comes before GROUP BY.

Brian M Over a year ago

@ThorstenKettner I have the group by in the query, I missed it in my initial post.

Collectives™ on Stack Overflow

SQL Counting values in a column

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related