CODEWITHSUNDEEP
t-sql
PositiveGuy
PositiveGuy

Reputation: 47763

T-SQL Count returns ALL records Instead of Subset

trying to figure out this dumb syntax issue

select distinct Count(Mgr) from CarsManager

returns all records..should be a subset count.

Upvotes: 0

Views: 158

Answers (2)

Thomas
Thomas

Reputation: 64655

Select Mgr, Count(*)
From CarsManager
Group By Mgr

You did not specify on what the subset count should be made. Given your example, I assumed it was the Mgr column.

If what you seek is a count of unique managers, then you can do:

Select Count(Distinct Mgr)
From CarsManager

Difference between Count(*) and Count(SomeColumn)

In comments, you asked about the difference between Count(*) and Count(SomeCol). The difference isn't in performance but logic. Count(*) counts rows regardless of column. Count(SomeCol) counts non-null values in SomeCol.

COUNT (Transact-SQL)

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

In this case of Count(SomeCol), ALL is implied.

Upvotes: 1

M Lamb
M Lamb

Reputation: 199

You can't do this all in one go - you need a subquery:

SELECT count(*) FROM (SELECT DISTINCT Mgr FROM CarsManager) as tbl1

Upvotes: 0

Related Questions