Welcome to WebmasterWorld Guest from 54.144.126.195

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)

5+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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)

5+ Year Member



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)

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



Did you try

select distinct (code).....

?

optik

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

5+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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?

 

Featured Threads

Hot Threads This Week

Hot Threads This Month