Re: SQL Question - Summing and Grouping

From: <mhr123_at_my-deja.com>
Date: Fri, 02 Feb 2001 08:07:36 GMT
Message-ID: <95dps6$cpg$1@nnrp1.deja.com>

In article <95cids$baj$1_at_nnrp1.deja.com>,   thomasm516_at_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/ Received on Fri Feb 02 2001 - 02:07:36 CST