Re: PHP/mySQL question about groups

On Feb 6, 2008 8:46 PM, Rob Gould <gouldimg@mac.com> wrote: > Let's say I have a PHP-based wine application, and it's taking a set of mySQL data > that looks like this: > > wineid name size > 123 Silver Oak 750ML > 123 Silver Oak 1.5L > 123 Silver Oak 1.5L > 456 Liberty School 750ML > 456 Liberty School 750ML > 456 Liberty School 750ML > 456 Liberty School 1.5L > > > If I do a: > > Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' > > I'd get: > > Silver Oak > > > However, what I'd REALLY like to return is: > > Silver Oak 750ML > Silver Oak 1.5L > > I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. > So there'd be a '750ML' group, and a '1.5L' group > > Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP > script that loops through the results and separates things manually. > That's something MySQL will allow that IMO it should not. Being able to use SELECT * and GROUP BY at the same time can create confusion as it did here. The other suggestions would probably work, but a good rule of thumb is not to use any columns in the SELECT clause unless they are either included in the GROUP BY clause or else use one of the aggregate functions like COUNT, SUM, AVG, etc. (I'm not sure of the actual SQL standard on this point, but SQL Server insists on it.) SELECT name, size FROM wine GROUP BY name, size Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROM wine Andrew