homepage Welcome to WebmasterWorld Guest from 54.211.137.77
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
A little help with "select group by"
nelsonm

5+ Year Member



 
Msg#: 4399281 posted 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

10+ Year Member



 
Msg#: 4399281 posted 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

5+ Year Member



 
Msg#: 4399281 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4399281 posted 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

5+ Year Member



 
Msg#: 4399281 posted 12:20 am on Dec 20, 2011 (gmt 0)

ok... thanks.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved