homepage Welcome to WebmasterWorld Guest from 54.196.197.153
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, Moderators: physics

Databases Forum

    
Using SQL GROUP BY along with empty values
optik




msg:4062892
 3:45 pm on Jan 18, 2010 (gmt 0)

Hi

I have entries in my database that have a column called product_code, not all the entries have a code and some have the same code as other e.g

name code
item 1
item 2
item 3 123
item 4 123
item 5 567

I have managed to use GROUP BY code to only show one result for duplicate entries for the field 'code' but when I do this the empty results are also grouped, how can I make GROUP BY ignore empty values?

And can I order the grouping to make sure the result for each set presented is the one I want?

 

physics




msg:4063056
 7:46 pm on Jan 18, 2010 (gmt 0)

Are you looking for something like this?

SELECT code, name FROM items WHERE code <> '' and code IS NOT NULL GROUP BY code ORDER BY name

optik




msg:4064381
 5:24 pm on Jan 20, 2010 (gmt 0)

I don't hink so as I do want to display rows that have no code, I just don't want no code to be seen as a duplicate by GROUP BY.

rocknbil




msg:4064496
 7:58 pm on Jan 20, 2010 (gmt 0)

Did you try

select distinct (code).....

?

optik




msg:4064640
 12:44 am on Jan 21, 2010 (gmt 0)

distinct does work when just selecting code but when I try and add other columns it doesn't work..

DISTINCT (code),name

I need to be able to get the data from the name column as well as the code column.

Dijkgraaf




msg:4069455
 12:31 am on Jan 28, 2010 (gmt 0)

When you are grouping by code, what are you doing with the names? As you need to aggregate them somehow if you are selecting both but grouping only by code.

Can you take your example data given above and tell us how you expect the results to look like?

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