Rasvet Rasvet - 4 months ago 6
SQL Question

How to calculate count of records by 2 columns

In

MySQL
I have a table.

Example:

id name type
1 Thomas 2
2 Thomas 2
3 Thomas 1
4 Paul 3
5 Paul 4
6 Paul 4


I need calculate same records by 2 columns.
Result for this example should be:

name type countOfRecords
Thomas 2 2
Thomas 1 1
Paul 3 1
Paul 4 2


Could you help me with this request?

Answer

Since you want records in your result set for each name and type <name,type> pair you need to group by name and type.

SELECT 
name,
type,
COUNT(*) countOfRecords
FROM your_table
GROUP BY name,type;

Note:

Group BY <some column> would generate a result set where number of rows = number of distinct / different / unique <some column>.

Same holds for multiple columns in GROUP BY clause.

Comments