CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3447031
user3447031

Reputation:

how to write this query in sql-server?

This is my table structure in sql-server:

id           Marks
----------   -----------
AAA          50
KKK          87
KKK          89
BBB          48
CCC          54
AAA          52
DDD          55
BBB          60
XXX          99

This is the desired output:

Name       attempts         Max Mark
-------    ----------------  ------------
AAA         2                 52
kkk         2                 89
BBB         2                 60
CCC         1                 54
DDD         1                 55
XXX         1                 99

I've tried this but it seems incorrect:

SELECT 
    name,
    count(*) as attempts,
    max(marks) 
FROM table_name 
GROUP BY name, attempts, max_marks

Upvotes: 1

Views: 62

Answers (2)

user275683
user275683

Reputation:

You were on the right track just added too much to grouping field,

SELECT id AS name
       ,COUNT(*) AS Attempts
       ,MAX(marks) as MaxMarks
    FROM table_name
    GROUP BY id

If your column is already part of aggregate function such as MAX or COUNT it does not need to be included in GROUP BY clause

Upvotes: 0

Christos
Christos

Reputation: 53958

Try this one:

SELECT 
    id AS Name,
    count(id) AS attempts,
    max(Marks) AS Max_Mark
FROM table_name 
GROUP BY id

Upvotes: 4

Related Questions