T-SQL count like string

Question

This is the select statement that I'm trying to use, and I'm trying to pull the count of all records that start with the phrase Unknown, but I'm getting an incorrect syntax error near the closing parenthesis of the count. How can I use count in this way?

SELECT DISTINCT [ID], COUNT(ID), COUNT([NAME] LIKE 'Unknown%')
FROM table.foo
GROUP BY [ID]

Put the condition in a Where clause.

SS_DBA
– SS_DBA

2017-10-02 14:01:30 +00:00
Commented Oct 2, 2017 at 14:01 — SS_DBA
– SS_DBA, Commented Oct 2, 2017 at 14:01

Gordon Linoff · Accepted Answer · 2017-10-02 14:01:22Z

6

You don't need SELECT DISTINCT with GROUP BY. Just use a CASE as the argument to SUM():

SELECT ID, COUNT(ID), 
       SUM(CASE WHEN NAME LIKE 'Unknown%' THEN 1 ELSE 0 END)
FROM table.foo
GROUP BY ID;

answered Oct 2, 2017 at 14:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

T-SQL count like string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related