MySQL Count Of Field Value

Question

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?

Vikdor · Accepted Answer · 2014-04-22 21:24:40Z

1

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

answered Apr 22, 2014 at 21:24

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

g_tec · Accepted Answer · 2014-04-22 21:25:35Z

0

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`

answered Apr 22, 2014 at 21:25

g_tec

6414 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL Count Of Field Value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related