CODEWITHSUNDEEP
sqlsql-servert-sql
vpn
vpn

Reputation: 41

Count the same value

I have a table like this:

Name     | Surname | Grade 
---------+---------+-------
Alex     | Smith   | A     
Bob      | Pratt   | B
Sean     | Colon   | C
Glenda   | Huffman | A
Laurence | Bryant  | D
Yousuf   | Lowry   | B
Gracie   | Coates  | C
Kenneth  | Khan    | A

I want to select name, surname, grade and count of students whith the same grade, so result should be like this:

Name     | Surname | Grade | Count
---------+---------+-------+------
Alex     | Smith   | A     | 3
Bob      | Pratt   | B     | 2
Sean     | Colon   | C     | 2
Glenda   | Huffman | A     | 3
Laurence | Bryant  | D     | 1
Yousuf   | Lowry   | B     | 2
Gracie   | Coates  | C     | 2
Kenneth  | Khan    | A     | 3

Can I do it in a single query?

Upvotes: 1

Views: 54

Answers (3)

Seyid Takele
Seyid Takele

Reputation: 1198

it is not in a single Query but here is the approch in mysql

SELECT t.*,count FROM vpns_table t 
left join (SELECT Grade,count(Grade) count FROM vpns_table group by Grade) t1 on t.Grade=t1.grade

Upvotes: 2

Vít Bednář
Vít Bednář

Reputation: 318

Try to use OVER Clause, more here: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

SELECT Name, Surname, Grade, COUNT(Grade) OVER(PARTITION BY Grade) AS Count FROM YourTable

Or you can use subselect, but subselect is performance killer.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You want a window function:

select t.*,
       count(*) over (partition by grade) as grade_count
from t;

Upvotes: 4

Related Questions