A little help with "select group by"

[nelsonm]

Hi all,

I have select statement...

$sql = 'SELECT IF(SUBSTRING(ip.Item, 4, 4)="Room","Room",ip.Item) AS Item';
$sql .= ' FROM `it-pricelist` AS ip';
$sql .= ' GROUP BY Item';

that is grouped by the item column, has a few non unique item values that contain "Room". I'm thinking that using the substring function to trim item field values containing "Room" to contain only "Room" as follows...

'01 Room' -> 'Room'
'02 Room' -> 'Room'
'03 Room' -> 'Room'
'Dryer'
'Vent'
'Dryer'

I want to "GROUP BY item" to get the following...

Room
Dryer
Vent

But i get...

Room
Room
Room
Dryer
Vent

what's wrong?

[mark_roach]

If you just want the distinct values then use the DISTINCT keyword and drop the group by.

eg.

$sql = 'SELECT DISTINCT IF(SUBSTRING(ip.Item, 4, 4)="Room","Room",ip.Item) AS Item';
$sql .= ' FROM `it-pricelist` AS ip';

Alternatively if you also want to know how many of each type there are add count(*) to your select statement :

$sql = 'SELECT IF(SUBSTRING(ip.Item, 4, 4)="Room","Room",ip.Item) AS Item, count(*)';
$sql .= ' FROM `it-pricelist` AS ip';
$sql .= ' GROUP BY Item';

[nelsonm]

Well from what i have read, DISTINCT and GROUP BY are supposed to be equivalent but DISTINCT worked as expected where GROUP BY did not with my SELECT method.

I now understand that, while GROUP BY can be used to omit duplicate records, normally GROUP BY is used when performing aggregate functions on a set of data and that DISTINCT Omits records that contain duplicate data in the selected fields. So i guess i used the wrong method to do what i intended to do - omit duplicate records.


thanks.

[Dijkgraaf]

The problem was that you had the following only in the select.
IF(SUBSTRING(ip.Item, 4, 4)="Room","Room",ip.Item) AS Item'
If you wanted everything with Room grouped you would also have needed that logic in the group by statement as that occurs before the select does.

[nelsonm]

ok... thanks.