SEO

#1 (**permalink**) 02-25-2008, 02:56 AM

Picture the scene,

I have a SUBSCRIBERS table thus

SUBS_ID number(8)
SUBS_TYPE varchar2(3)
SUBS_START date
SUBS_END date

I am wanting to run a query which will give me a count of all active
and cancelled subscriptions by type. An active subscription is one with
a NULL SUBS_END date and cancelled is one with a SUBS_END date.

Have been trying in-line views to no avail.

All help greatly appreciated.

#2 (**permalink**) 02-25-2008, 02:56 AM

Have to add that i have simplified the selection criteria for this
post....there are other conditions to apply to the SUB_START and
SUB_END dates to determine whether a subscription is active or
not....just didnt want to clutter the query

Also running on 9.2.0.4

#3 (**permalink**) 02-25-2008, 02:56 AM

Miggins wrote:
> Have to add that i have simplified the selection criteria for this
> post....there are other conditions to apply to the SUB_START and
> SUB_END dates to determine whether a subscription is active or
> not....just didnt want to clutter the query
>
> Also running on 9.2.0.4

# untested
select case subs_end when NULL then 'active' else 'cancelled' end "type",
count(*)
from ...
group by case subs_end when NULL then 'active' else 'cancelled' end

robert

#4 (**permalink**) 02-25-2008, 02:56 AM

"Robert Klemme" <bob.news@gmx.net> a écrit dans le message de news: 47b1o4Feqs52U1@individual.net...
| Miggins wrote:
| > Have to add that i have simplified the selection criteria for this
| > post....there are other conditions to apply to the SUB_START and
| > SUB_END dates to determine whether a subscription is active or
| > not....just didnt want to clutter the query
| >
| > Also running on 9.2.0.4
|
| # untested
| select case subs_end when NULL then 'active' else 'cancelled' end "type",
| count(*)
| from ...
| group by case subs_end when NULL then 'active' else 'cancelled' end
|
| robert
|

When answer is trivial, this is surely a homework question.
Take care to only give an indication and not a solution.

You know the story about the fish...

Regards
Michel Cadot

#5 (**permalink**) 02-25-2008, 02:58 AM

Michel Cadot wrote:
> "Robert Klemme" <bob.news@gmx.net> a écrit dans le message de news:
> 47b1o4Feqs52U1@individual.net...
>> Miggins wrote:
>>> Have to add that i have simplified the selection criteria for this
>>> post....there are other conditions to apply to the SUB_START and
>>> SUB_END dates to determine whether a subscription is active or
>>> not....just didnt want to clutter the query
>>>
>>> Also running on 9.2.0.4
>>
>> # untested
>> select case subs_end when NULL then 'active' else 'cancelled' end
>> "type", count(*)
>> from ...
>> group by case subs_end when NULL then 'active' else 'cancelled' end
>>
>> robert
>>
>
> When answer is trivial, this is surely a homework question.
> Take care to only give an indication and not a solution.

I usually do but I didn't view this as HW assignment.

> You know the story about the fish...

:-)

Thanks for the reminder anyway!

robert

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

In line queries......