CODEWITHSUNDEEP
mysqlsqldatabase
user3165675
user3165675

Reputation: 45

Select with inner distincts

I use MSSQL and I a table like this:

ID   |   Value1   |   Value2 
1    |     ABC    |   AAA 
2    |     ABC    |   AAA
3    |     XYZ    |   AAA
4    |     ABC    |   BBB
5    |     ABC    |   BBB
6    |     ABC    |   BBB

Now I want to count, based on Value2 how many times I have the same entry in Value1. In practical terms, I need a resulting Select like following:

 Value2  |   Value1   |  Count
   AAA   |     ABC    |    2
   AAA   |     XYZ    |    1
   BBB   |     ABC    |    3

I hope its clear enough? It should work with 2 distincts, right?

Upvotes: 1

Views: 44

Answers (2)

G one
G one

Reputation: 2729

select value2, value1, count(*)
from table1
group by value2, value1

fiddle

Upvotes: 2

Sai Avinash
Sai Avinash

Reputation: 4753

Can you please try this:

select value2,value1,count(*) as Count from tablename group by Value2 ,value1

group by is an aggregate operator used for grouping rows

Hope this helps..

Upvotes: 1

Related Questions