Toad World® Forums

Querry


#1

suppose there is an office id column with 10 values and having billing status of ‘c’,‘l’,‘d’,‘i’,‘n’ i want the querry for the count using the decode function and the count should be equal to the office_id consumer numbers.


#2

Hi Sam72,

On 13/07/15 09:40, sam72 wrote:

suppose there is an office id column with 10 values and having billing

status of ‘c’,‘l’,‘d’,‘i’,‘n’ i want the querry for the count using the

decode function and the count should be equal to the office_id consumer

numbers.

What I think you are asking for is this:

A table has an office_id column with 10 different values, and a

billing_status column with values of ‘c’, ‘l’, ‘d’, ‘i’, ‘n’.

You are wanting the query that will return the counts for each

office_id, each status within converted to some meaningful text using

decode().

You would be looking at something like:

select office_id, billing_status, count(*)

from (

select office_id,

decode(billing_status,

‘c’, ‘Completed’,

‘l’, ‘Late’,

‘d’, ‘Delayed’,

‘i’, ‘Invoiced’,

‘n’, ‘New’,

‘Unknown’) as billing_status

from a_table

)

group by office_id, billing_status

order by office_id, billing_status;

I’ve done it as above, with a nested select to get the office_id and

converting the billing_status from a single code to a text message as,

if not, I would have had to group by the entire DECODE statement again,

and I’m basically lazy! I can’t be bothered with all that typing.

If you need it in a single statement, it would need to be something like

the following:

select office_id,

decode(billing_status,

‘c’, ‘Completed’,

‘l’, ‘Late’,

‘d’, ‘Delayed’,

‘i’, ‘Invoiced’,

‘n’, ‘New’,

‘Unknown’) as billing_status,

count(*)

from a_table

group by office_id,

decode(billing_status,

‘c’, ‘Completed’,

‘l’, ‘Late’,

‘d’, ‘Delayed’,

‘i’, ‘Invoiced’,

‘n’, ‘New’,

‘Unknown’)

order by 1,2;

There, I typed it!

I’m away from my databases at the moment, so the above is off the top of

my head, and very much untested!

HTH

Cheers,

Norm. [TeamT]