Welcome to WebmasterWorld Guest from 54.163.142.67

Forum Moderators: open

Message Too Old, No Replies

Using SQL GROUP BY along with empty values

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

Full Member

5+ Year Member

joined:Aug 17, 2007
posts:320
votes: 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?

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 27, 2001
posts:2548
votes: 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

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

Full Member

5+ Year Member

joined:Aug 17, 2007
posts:320
votes: 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.
7:58 pm on Jan 20, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Did you try

select distinct (code).....

?

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

Full Member

5+ Year Member

joined:Aug 17, 2007
posts:320
votes: 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.

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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?