user2772568 user2772568 - 2 months ago 6
SQL Question

query for selecting distinct names and count names in sql

In my table 2 columns are there.

Name
and
Marks
. Something like this.

Name Marks
---------- -----------
AAA 50
BBB 48
CCC 54
AAA 52
DDD 55
BBB 60
AAA 66


I need to retrieve from the table something like below

Name No.of.attempts Max Mark
------- ---------------- ------------
AAA 3 66
BBB 2 60
CCC 1 54
DDD 1 55

Answer

You should do like:

select name,count(name) as no_of_attempts,max(marks) 
from table_name 
group by name

fddle demo here