CODEWITHSUNDEEP
t-sql
dnatoli
dnatoli

Reputation: 7012

Count number of rows with distinct value in column in T-SQL

In T-SQL, how can I query this table to show me record counts based on how many times a distinct value appears in a column?

For example, I have a table defined as:

ControlSystemHierarchy
----------------------
ParentDeviceID int
ChildDeviceID int
Instrument bit

I want to display the number of records that match each distinct ParentDeviceID in the table so that this table

ParentDeviceID  | ChildDeviceID  | Instrument
1               | 1              | 0
1               | 2              | 0
1               | 2              | 1
2               | 3              | 0

would return

ParentDeviceID | Count
1              | 3
2              | 1

Upvotes: 1

Views: 6506

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select ParentDeviceID, count(*) as [Count]
from ControlSystemHierarchy
group by ParentDeviceID

Upvotes: 3

Related Questions