MySQL – Selecting Distinct Summed Column with HAVING Clause

distinctMySQLsum

I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a

SELECT
    t1.a - sum(t2.a) as a,
    t1.b - sum(t2.b) as b
FROM
    t1 inner join t2 using(id)
GROUP BY
    t1.id
HAVING
    a > 0
ORDER BY
    b ASC
LIMIT 
    15

Is there any other way then bagging the whole query in another SELECT disctint b (...) as t ?

Best Answer

If I understand correctly and you only want DISTINCT b values, you just need to remove the a column from the SELECT list and use SELECT DISTINCT:

SELECT DISTINCT
    t1.b - sum(t2.b) AS b
FROM
    t1 INNER JOIN t2 USING (id)
GROUP BY
    t1.id, t1.a, t1.b
HAVING
    t1.a - sum(t2.a) > 0
ORDER BY
    b ASC
LIMIT 
    15 ;
Related Question