question on "group by"

[sfullman]

Ok, so I'm familiar with the group by clause. I'm trying to condense a large table of records and I want to find out what values are in a field in a given group.

I know I can choose

COUNT(distinct FIELDNAME) AS distinctItems

and distinctItems will give me a count of unique values.

Is there any formula to return a LIST of those values in the format of say

'Acura', 'Buick', 'Chevrolet', ..

This would really help my analysis of the table.

Sincerely,
Sam Fullman

[Lars_Berg]

Not with Mysql. You can do it in the client easily if the result is properly ordered.

[sfullman]

So you're saying create a script for this purpose?

Sam

[[deleted]]

You'll have to run a seperate query for that.

The GROUP BY is used specifically to get aggregates; information about a group of records together. Once you have GROUPed, you can nolonger get information about the records in that group seperately, only about the group as a whole.

This is a very common mistake that mysql-users make, they GROUP BY some column of a table, like a userid in a purchase-order table, and then select the orderprice from the same table. But because one userid can have many orders, it is impossible to select the price, SQL cannot know which one to select.

[steved]

Sounds to me like what you really need to do is run a query like:

Select distinct FieldName from TableName

That will return a record set with the values you are after.