CODEWITHSUNDEEP
mysqlsql
user3362278
user3362278

Reputation: 17

MySQL Count Of Field Value

I have a MySQL view with the fields id and set. Because it's a view, most ids are repeated and have duplicate entries. For example, and id = 120158 may have 5 rows, 3 where set = A and 2 where set = B. I want to run a query off of the view to display the number of rows each id has associated with its corresponding sets. I tried:

SELECT `id`, 
    `set`,
    (SELECT COUNT(set)) AS `CountOfSet`
FROM `view1`

However, this simply returns the same view (duplicate rows still exist) with CountOfSet equal to 1 for every row. Any ideas?

Upvotes: 0

Views: 51

Answers (2)

g_tec
g_tec

Reputation: 641

You need to group your data using GROUP BY clause:

GROUP BY `id`, `set`

Composing all together:

SELECT `id`, 
`set`,
COUNT(*) AS `CountOfSet`
FROM `view1`
GROUP BY `id`, `set`

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

You should be grouping your results by id and set to get the desired result:

SELECT `id`, `set`, COUNT(*) AS `CountOfSet` FROM `view1`
GROUP BY `id`, `set`

This would return the results as 

120158 A 3
120158 B 2

Upvotes: 1

Related Questions