order by in decode

[sathyagi]

Hi,

I'm using decode in the select clause in order to count the number of members in each group and need to display the members exactly in the same way shown in decode. (the order in neither ascending or descending)

Please let me know how to solve this issue.

Thanks in advance!

Sathya

[rmiao]

Have any samples?

[sathyagi]

Here is the example of what I need. The output should be exactly the same as shown below.

select decode(upper(group_id), 'W','White','B','Black','O','Orange','G','Green',' P','Pink','B','Blue'
from table_a
group by group_id

Output:
-------
White 450
Black 590
Orange 920
Green 260
Pink 370
Blue 765

Thanks,
Sathya

[skhanal]

select decode(upper(group_id), 'W','White','B','Black','O','Orange','G','Green',' P','Pink','B','Blue'), count(*)
from table_a
group by group_id
order by decode(upper(group_id), 'W',1,'B',1,'O',3,'G',4,' P',5,'B',6) ASC

This may still not work as for B you have two possible values Black or Blue, decode will always evaluate B as black.

[sathyagi]

Hi skhanal,

That works great! Thanks a lot.
I have one more question -- whenever there is zero count in any of the colors(White,Black,Green,etc...) it's not seen in the output. I woule like to see all the colors even if it is zero.

Thanks!

Sathya

[skhanal]

You may have to create a table with all the colors you want to use a OUTER JOIN to join to table_a and use 0 for all NULL values in count(*) column.

[sathyagi]

The count zero for each color differs everytime. I cannot point out any particualr color that will be zero everytime.

Thanks,
Sathya

[sathyagi]

Can anyone help me on this query, please?