CODEWITHSUNDEEP
c#sqldatabasems-accessms-access-2007
Usama Khan
Usama Khan

Reputation: 19

How to Select Distinct records then count them in C# Access database?

I have a table called TableA

Table A

I want my results to be like this

Results

My code

       string query = "Select Column2,Count(*) from (Select DISTINCT Column1 from TableA Group by Column2)"

I have tried a lot of solutions for apparently simple task but in vain. Been stuck from couple of hours. wish Access 2007 allow a simple Count(Distinct Column1) etc but it doesn't. Help and guidance is required.

Upvotes: 0

Views: 153

Answers (2)

Usama Khan
Usama Khan

Reputation: 19

So dbmitch comment gave me headstart, I just changed it a bit. Here is the solution:

"Select Column2,Count(T1.Column1) AS Column3 from (Select Column2,Column1 from TableA Group by Column1,Column2) AS T1 Group by T1.Column2"

Upvotes: 1

mailicreate
mailicreate

Reputation: 144

Try this:

SELECT column2, Count(*) AS C FROM (SELECT DISTINCT column1 FROM TableA GROUP BY column2) AS D;

Upvotes: 0

Related Questions