Topic: Let MySQL do some lifting (Page 1 of 1)

I've been trying to better utilize mysql for some of the workload on my projects. And I've run into a spot where I'm not sure if it can be done or not. Basically, I've got a table with an ID for groups (not unique) and a flag for finished or not finished (zero or one). What I'm looking to do is select everything where there's 3 or more finished from the same group. So it could go across multiple groups but say if there's only two entries for group "foo" that have finished, "foo" entries are excluded entirely.

If I were to go about this with my currently knowledge I would run through the table once and populate another table with ID's that have 3 or more finished, and then go off that. But I'm wondering could it be done with just the query?

sure...
select id, count(flag) from table group by id having count(flag) > 3
should be what you want...

Excellent! I'm going to have to try that first thing tonight... thanks TP!

Hey TP thanks again for your help... This is close but not doing the trick. Just a couple questions.

I did some research on COUNT and it appears that it is counting the number of rows? Or could I do something like COUNT(flag = 1) ?

This query you provided yields perfect results for the number of groups, but I'm hoping to get all rows within that group. Does that make sense?

Example:
- Group A has 60 entries, 53 finished
- Group B has 6 entries, 2 finished
- Group C has 10 entries, 10 finished

Hopefully I could get 63 rows out of the above data instead of 2. Compromising of group A and group C's finished entries because they have at least 3 finished.

well, that's not quite how sql aggregation works as far as I know.
You create aggregate rows with group by, and some functions (such as count)
and the having clause work on these aggregate groups.

Anyhow, here are some ideas:

-use sub queries ( if you're not using mysql, that is),
-use multiple queries - the first to get a list of groups, the second to get anything in those groups.
-fancy joining... but this is beyond me right now, but the back of my head seems to say that
most sub queries actually can be turned into joins.

sorry I can't be of more help,
so long,

->Tyberius Prime

quote:

---

Tyberius Prime said:

sorry I can't be of more help

---

Not at all... you're extremely helpful. I've already worked through a way of using multiple queries that works (as suggested). Thanks again!