C# SQL table select and count distinct values

Question

i have ASP website with SQL database table. in this table column mane "type". i want to get all distinct values from this column with values count in datatable. for example for database table:

id  type
---------
1   type1
2   type2
3   type3
4   type2
5   type2
6   type3

i want to get the following datatable:

type  count
------------
type1   1
type2   3
type3   2

Select Type,Count(type) from TableName Order by Type GroupBy Type — Shekhar Pankaj, Aug 03 '15 at 07:44

score 3 · Accepted Answer · answered Aug 03 '15 at 07:49

3

You can use

SELECT type, COUNT(type) as count
FROM Table1 
GROUP BY type

Result

|  type | count |
|-------|-------|
| type1 |     1 |
| type2 |     3 |
| type3 |     2 |

SqlFiddle DEMO

answered Aug 03 '15 at 07:49

Soner Gönül

97,193
102
206
364

Very helpful answer Thank you. In addition to that could you please show how to write this query in Linq? – Abdulhakim Zeinu Jan 10 '20 at 06:04
@AbdulhakimZeinu You can check this answer for that: https://stackoverflow.com/q/296972/447156 – Soner Gönül Jan 10 '20 at 07:27

Nikhil Agrawal · Answer 2 · 2015-08-03T08:17:20.033

0

I would suggest that you do this in sql itself by using following query

SELECT type, COUNT(type) as count
FROM myTableName 
GROUP BY type

edited Aug 03 '15 at 08:17

answered Aug 03 '15 at 07:43

Nikhil Agrawal

47,018
22
121
208

it doesnt count the values.i get all count as '1' – user281812 Aug 03 '15 at 07:54

score 0 · Answer 3 · answered Jan 10 '20 at 08:14

0

SQL

SELECT type, COUNT(type) as count
FROM Table1 
GROUP BY type

Linq

from t in <YourEntityObject>.YourTableName 
group t by t.type into myVar 
select new
   {
         k = myVar.Key,
         c = myVar.Count()
   };

answered Jan 10 '20 at 08:14

Abdulhakim Zeinu

3,333
1
30
37

C# SQL table select and count distinct values

3 Answers3