From: mhr123@my-deja.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: SQL Question - Summing and Grouping
Date: Fri, 02 Feb 2001 08:07:36 GMT
Organization: Deja.com
Lines: 95
Message-ID: <95dps6$cpg$1@nnrp1.deja.com>
References: <95cids$baj$1@nnrp1.deja.com>
NNTP-Posting-Host: 195.249.17.6
X-Article-Creation-Date: Fri Feb 02 08:07:36 2001 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT)
X-Http-Proxy: 1.0 VIRUSWALL, 1.0 x60.deja.com:80 (Squid/1.1.22) for client 195.249.17.6
X-MyDeja-Info: XMYDJUIDmhr123


In article <95cids$baj$1@nnrp1.deja.com>,
  thomasm516@my-deja.com wrote:
> I'm new to SQL, but I do have some programming background.  I have two
> questions.
>
> 1) First, a quick one.  Does Oracle have a news server that I can get
> through my newsreader (I'm using this group through Deja)?  Barring
> that, does anyone know if it's possible to connect to the Deja news
> server with a newsreader, so that I don't need to go through the
> Deja.com web site?
>
> 2) This is my main question.
>
> I need to create a report that sums the cost savings for hospital
> patients and groups the cases into dollar ranges.  The ranges are
>
> <=$2,000
> $2,001 - $4,000
> $4,001 - $6,000
> $6,001 - $8,000
> $8,001 - $10,000
> >$10,000
>
> The output should look like:
>
> Cases with $2,000 or less in cost savings
> Pat4 1824
>
> Cases with $2,001 - $4,000 in cost savings
> Pat3 3948
>
> Cases with $4,001 - $6,000 in cost savings
> Pat1 4634
> Pat2 5472
>
> I've got my report so that it sums the figures for each patient, but
> I'm doing that through the report form, which is preventing me from
> subsequently grouping the cases into the given dollar ranges.  Is
 there
> a way through SQL to sum the cost savings on each case, then group the
> cases into the required dollar ranges?
>
> Any help you can offer will be greatly appreciated!
>
> --Tom
>
> Sent via Deja.com
> http://www.deja.com/
>

Hi,

Don't know answer to 1), but here are some ideas for 2).

Without knowing your exact requirements and possibilities, this might
help you some of the way. The code is a SQL*Plus report which is meant
as a stub for you own refinements, if you can use it. In my example, I
have a table PATIENTS, with two columns PAT and SAVING:


clear breaks
break on savings

select decode(sign(2000-p.saving),1,'Cases with $2,000 or less in cost
savings',
        decode(sign(4000-p.saving),1,'Cases with $2,001 - $4,000 in
cost savings',
         decode(sign(6000-p.saving),1,'Cases with $4,001 - $6,000 in
cost savings',
          decode(sign(8000-p.saving),1,'Cases with $6,001 - $8,000 in
cost savings',
           decode(sign(10000-p.saving),1,'Cases with $8,001 - $10,000
in cost savings',
           'Cases with $10,001 or more in cost savings'))))) savings,
       p.pat, p.saving
from   patients p
order by 1;

The output looks something like:

SAVINGS                                     PAT       SAVING
------------------------------------------- ----- ----------
Cases with $2,000 or less in cost savings   Pat4        1824
Cases with $2,001 - $4,000 in cost savings  Pat3        3948
Cases with $4,001 - $6,000 in cost savings  Pat1        4634
                                            Pat2        5472

If this does not suit you, you might find it feasible to investigate
more sophisticated methods based on PL/SQL rather than plain Selects.

Regards, Michael


Sent via Deja.com
http://www.deja.com/