CODEWITHSUNDEEP
mysql
honeybadger
honeybadger

Reputation: 37

Count number of rows that are at the minimum of a group MySQL

I have a table that contains all the distinct names from another table, and then a column that holds the minimum value that the name received. For example, the table looks like this:

table1
Name | min_value
a    | 1
b    | 2
c    | 4

The original table looks like this:

table2
Name | value
a    | 1
b    | 2
c    | 4
a    | 2
c    | 8
a    | 1

I want to return the number of times that the minimum value occurs within the original table. So, in this example it would return something like this:

output_table
Name | times_at_min
a    | 2
b    | 1
c    | 1

Any help would be appreciated, thanks in advance.

Upvotes: 0

Views: 209

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

One way to do it is:

SELECT m.Name,
       min_value,
       COUNT(CASE WHEN m.value = min_value THEN 1 END) As times_at_min
FROM mytable AS m
INNER JOIN (
   SELECT Name, 
          MIN(value) AS min_value       
   FROM mytable
   GROUP BY Name ) AS g
ON m.Name = g.Name
GROUP BY Name

In a subquery you select MIN(value) per Name. Joining with the derived table of this subquery, you can perform conditional aggregation so as to calculate the number of times the minimum value appears in the original table.

Fiddle demo here

Upvotes: 1

Related Questions