| A little help with "select group by"
|
nelsonm
msg:4399283 | 6:48 am on Dec 19, 2011 (gmt 0) |
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
msg:4399396 | 2:12 pm on Dec 19, 2011 (gmt 0) |
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
msg:4399409 | 3:07 pm on Dec 19, 2011 (gmt 0) |
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
msg:4399489 | 8:11 pm on Dec 19, 2011 (gmt 0) |
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
msg:4399553 | 12:20 am on Dec 20, 2011 (gmt 0) |
ok... thanks.
|
|
|
