Using SQL GROUP BY along with empty values

Forum Moderators: open

Message Too Old, No Replies

Using SQL GROUP BY along with empty values

optik

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

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

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

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

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

Did you try

select distinct (code).....

optik

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

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?