CODEWITHSUNDEEP
mysqlgroup-byaggregate-functions
puyol5
puyol5

Reputation: 79

Use MySQL aggregate functions for comparison

Ok, in a MySQL database I have this table:

id | value
 1 | 141
 1 | 142
 1 | 143
 2 | 144
 2 | 145
 2 | 148

I want to group the table by the id and show the number of rows where the value is lower than the minimum value plus 3.

Expected output for the table above would be:

id | counted
 1 | 3
 2 | 2

I tried these queries but none of them works:

SELECT id, COUNT(*) counted FROM table WHERE value < MIN(value)+3 GROUP BY id

SELECT id, COUNT(*) counted FROM table GROUP BY id HAVING value < MIN(value)+3

SELECT id, SUM(CASE WHEN value < MIN(value)+3 THEN 1 ELSE 0 END) counted FROM table GROUP BY id

Any ideas?

Upvotes: 0

Views: 300

Answers (1)

Aziz
Aziz

Reputation: 20735

SELECT table.id id, 
       COUNT(*) counted
FROM   table 
       LEFT JOIN (SELECT id, 
                         MIN(value) m 
                  FROM   table 
                  GROUP  BY id) tmp 
              ON table.id = tmp.id 
WHERE  table.value < tmp.m + 3 
GROUP  BY table.id;

The idea is to left join the minimum value for each id, then compare the two rows (value vs. min-value). The temporary table that results from the join looks like this:

  table.   |    tmp.
id | value | id  |  m 
-----------------------
 1 |  141  |  1  |  141
 1 |  142  |  1  |  141
 1 |  143  |  1  |  141
 2 |  144  |  2  |  144
 2 |  145  |  2  |  144
 2 |  148  |  2  |  144

the output of the above query is:

id | counted
 1 | 3
 2 | 2

Upvotes: 1

Related Questions