Closed Thread Icon

Topic awaiting preservation: Let MySQL do some lifting Pages that link to <a href="https://ozoneasylum.com/backlink?for=30017" title="Pages that link to Topic awaiting preservation: Let MySQL do some lifting" rel="nofollow" >Topic awaiting preservation: Let MySQL do some lifting\

 
Author Thread
redroy
Paranoid (IV) Inmate

From: 1393
Insane since: Dec 2003

posted posted 02-22-2008 04:41

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?

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 02-22-2008 09:35

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

redroy
Paranoid (IV) Inmate

From: 1393
Insane since: Dec 2003

posted posted 02-22-2008 21:45

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

redroy
Paranoid (IV) Inmate

From: 1393
Insane since: Dec 2003

posted posted 02-23-2008 17:18

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.

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 02-24-2008 18:04

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

redroy
Paranoid (IV) Inmate

From: 1393
Insane since: Dec 2003

posted posted 02-25-2008 05:19
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!

« BackwardsOnwards »

Show Forum Drop Down Menu