[RESOLVED] Group By problem

robkir

I can't seem to get the SQL group by to work correctly. I am grouping on the field whatDiv (which is something like 10G or 10😎. In my test data, I know there are three instances of 10G--but the SELECT statement only picks up 2. What am I missing?

SELECT * from master_game_sched 
     WHERE `tourn_ID`= 1 
          AND gameDate='20130421' 
              AND home >'0' 
                  group by whatDiv, flight 
                        order by whatDiv

I've used group by successfully before...never had this problem. Not sure what else to try.

Thanks.

Weedpacket

One thing that can cause grief is if you are selecting any columns that neither appear in the GROUP BY clause nor have any aggregate functions applied to them. In fact, most DBMSs would refuse to run such a query. In this case, the columns that would cause a problem would be everything in the master_game_sched table except 'whatDiv' and 'flight' - what values should be used for those columns in the result?

But since you aren't using any aggregate functions, there's no reason for a GROUP BY clause in the first place - and GROUP BY probably has nothing to do with what you want in the first place.

robkir

Weedpacket;11025789 wrote:
GROUP BY probably has nothing to do with what you want in the first place.

Actually Groups was the answer. The SQL was doing exactly what I requested. I had failed to notice a third factor that needed to be included in the group by statement. Once that was corrected, the module works perfectly.

Thanks for the comments.

Cheers,
robkir

Weedpacket

robkir wrote:
Actually Groups was the answer. The SQL was doing exactly what I requested.

Well, as I mentioned any sane RDBMS would reject that query as being ill-specified (and even in MySQL the behaviour can be turned off). After you've grouped on whatDiv and flight, what goes in all the other columns that you're selecting? If you're not using them, why are you selecting them? If you are using them, how are you controlling which values appear in them? (In the words of the MySQL manual, "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. ")

Are you sure you're not actually after SELECT DISTINCT?

robkir

what goes in all the other columns that you're selecting?

Your point is well taken, the other columns are not really used in this module. This table holds soccer game details. The module's job is to show where the whatDivs (like U10B -- for under 10 year old boys) play their soccer games. Typically a location would have 20 to 50 games. I used the GROUP BY so it would only echo a whatDiv, flight, and siteID once. Some of the game divisions are so large that they are split up by flights at various locations. To be honest, it didn't occur to me to call out the fields. I was focusing on trying to get the info to report correctly. So other than not specifying the fields, is there anything else wrong with the structure?

I looked at DISTINCT but I had three fields to group on, so GROUP BY seemed like the route to go.

Derokorian

DISTINCT means that the columns selected will be unique combinations for all rows returned. AKA you will never get U10B, Flight2, SITE167 twice using DISTINCT.

robkir

DISTINCT means that the columns selected will be unique combinations for all rows returned. AKA you will never get U10B, Flight2, SITE167 twice using DISTINCT.

I got it...thanks.

I appreciate all the help.

robkir