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.
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]