MYSQL Group By with value > 0

Question

I've been working on this query and it's working as intended by displaying one output per p.ID since I'm grouping by p.ID (since there would be multiple p.ID values without the grouping.) However, depending on the p.ID, I will sometimes get a value of e.alternateEventDurationInMilliseconds that is not the fastest e.alternateEventDurationInMilliseconds.

Is there a way I could still use the GROUP BY p.ID but also add a check to get the minimum e.alternateEventDurationInMilliseconds as long as the duration is still > 0? Otherwise, I'll be given a random value by default that is greater than 0, but I want to specifically get the minimum duration that is greater than 0, with only one row per p.ID.

Thanks! :)

SELECT
    p.ID,
    e.personaId,
    e.EVENTID,
    e.alternateEventDurationInMilliseconds                  
FROM
    EVENT_DATA e
INNER JOIN
    PERSONA p ON e.personaId = p.ID
WHERE
    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > '0'
GROUP BY
    p.ID
ORDER BY
    e.`alternateEventDurationInMilliseconds` ASC

can you please provide your expected output in table format and also which version of mysql you are using — Fahmi, Nov 08 '18 at 04:59
I think you are after the [MIN()](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_min) aggregate method. But please, provide a sample of data and expected output. — Shidersz, Nov 08 '18 at 05:07
@fa06 Hopefully this helps visualize what I'm trying to explain. The output will look like this. With the GROUP BY in my query, I will get one value per p.ID (which is what I want). However, in that same image on the bottom two rows, that is what is shown if I take out the GROUP BY. So, you can see that Person1 actually has a faster duration, but it's not shown with the GROUP BY since it's grouping by any time > 0 instead of also grouping by the lowest time > 0. https://imgur.com/a/bqj4cKp — ccc, Nov 08 '18 at 05:08
Your query is invalid SQL and it is rejected by most RDBMS-es except MySQL. Read [this answer](https://stackoverflow.com/a/35748555/4265352) for an explanation why it is invalid and how MySQL handles it and why it returns values that do not match your expectations. — axiac, Nov 08 '18 at 05:34

score 1 · Answer 1 · answered Nov 08 '18 at 05:13

You'll have to make a new table in a subquery and then join to that table.

This is the query that will give you the e.id and min (but not zero) data you're looking for:

select id, personalID, EVENTID,
min(alternateEventDurationInMilliseconds) as mill,
from EVENT_DATA
 where alternateEventDurationInMilliseconds > 0
 and e.EVENTID=43
 group by personaId;

so you should use that table that is created by the subquery as a joined table instead of joining to EVENT_DATE, something like:

join ( select id, personalID, e.EVENTID,
min(alternateEventDurationInMilliseconds) as mill,
from EVENT_DATA
 where alternateEventDurationInMilliseconds > 0
 and e.EVENTID=43
 group by personaId ) e...

I think that should work.

This may also work, but I'm not sure - just take your previous query and change e.alternateEventDurationInMilliseconds to "min(e.alternateEventDurationInMilliseconds) as e.alternateEventDurationInMilliseconds" to your original query.

Shidersz · Accepted Answer · 2018-11-08T05:35:12.240

You have to use the MIN() aggregate function to select the minimun value (of a particular column) of each group after grouping. Also, why you need to join table PERSONA if you only are retrieving the ID from that table, but this ID is already part of the table EVENT_DATA (on column personaId). The query you may need is this one:

SELECT
    p.ID,
    MIN(e.personaId), -- This could be replaced by ANY_VALUE(e.personaId).
    MIN(e.EVENTID), -- This could be replaced by ANY_VALUE(e.EVENTID).
    MIN(e.alternateEventDurationInMilliseconds) AS minAlternateEventDurationInMilliseconds
FROM
    EVENT_DATA e
INNER JOIN
    PERSONA p ON e.personaId = p.ID
WHERE
    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > 0
GROUP BY
    p.ID
ORDER BY
    minAlternateEventDurationInMilliseconds ASC

If you don't need other data from table PERSONA with the exception of the ID, previous query could be simplified to this:

SELECT
    MIN(e.personaId), -- This could be replaced by ANY_VALUE(e.personaId).
    MIN(e.EVENTID), -- This could be replaced by ANY_VALUE(e.EVENTID).
    MIN(e.alternateEventDurationInMilliseconds) AS minAlternateEventDurationInMilliseconds
FROM
    EVENT_DATA e
WHERE
    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > 0
GROUP BY
    e.personaId
ORDER BY
    minAlternateEventDurationInMilliseconds ASC

score 0 · Answer 3 · answered Nov 08 '18 at 05:09

You can use this: (assuming mysql)

SELECT p.ID, e.alternateEventDurationInMilliseconds from persona p
join (select min(alternateEventDurationInMilliseconds) as alternateEventDurationInMilliseconds, personaid
from event_data where personaid = '43' and alternateEventDurationInMilliseconds > 0 
group by personaid) as e on e.personaid = p.Id;

This is a famous problem:

The Rows Holding the Group-wise Maximum of a Certain Column

MYSQL Group By with value > 0

3 Answers3