Creating different New Fields Based on Calculation

Hi,
I am VERY new to SQL and I do not know if I am word this question correctly,
so forgive me.
I would like to create a new table that does calculations on some of the
same fields.
? IPA number (from the IPA_Num field)
? Total number of Rx's (sum of rx field )
? Total Paid( sum of amt_paid field)
? Total generic paid (If the field drugs = 1 then sum the amt_paid field)
? Percent generic (If the field drugs = 1 then sum and divide by the sum o
f
the field rx)
? Percent formulary (if the formulary field = A then sum and divide by th
e
rx field)
? Cost difference ( Average amount paid per brand Rx - average amount
paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
YvonneHere is some info to get you started.
select [IPA number (from the IPA_Num field)]=IPA_Num,
[Total number of Rx's (sum of rx field )]=sum(rx),
[Total Paid( sum of amt_paid field)]=sum(amt_paid),
[Total generic paid (If the field drugs = 1 then sum the amt_paid
field)]=sum(case when drugs=1 then amt_paid else 0 end),
[Percent generic (If the field drugs = 1 then sum and divide by the sum of
the field rx)]=sum(case when drugs=1 then amt_paid else 0 end)/sum(rx),
[Percent formulary (if the formulary field = A then sum and divide by the
rx field)]=sum(case when formulary='A' then amt_paid else 0 end)/sum(rx),
[Cost difference ( Average amount paid per brand Rx - average amount
paid per generic Rx )]=Avg(rx)-Avg(case when drugs=1 then amt_paid else 0
end)
from your_table
group by IPA_Num
Btw, I suggest you post DDL (create table) + sample data (insert) + expected
output here. It would be much easier for us to help you.
-oj
"HasanaMonique" <HasanaMonique@.discussions.microsoft.com> wrote in message
news:2D71E579-4920-4366-A09F-A66013FCE824@.microsoft.com...
> Hi,
> I am VERY new to SQL and I do not know if I am word this question
> correctly,
> so forgive me.
> I would like to create a new table that does calculations on some of the
> same fields.
> . IPA number (from the IPA_Num field)
> . Total number of Rx's (sum of rx field )
> . Total Paid( sum of amt_paid field)
> . Total generic paid (If the field drugs = 1 then sum the amt_paid field)
> . Percent generic (If the field drugs = 1 then sum and divide by the sum
> of
> the field rx)
> . Percent formulary (if the formulary field = A then sum and divide by
> the
> rx field)
> . Cost difference ( Average amount paid per brand Rx - average amount
> paid per generic Rx )
> Any help is greatly appreciated.
> Blessing to all
> Yvonne
>|||Thank you. I am truly grateful for your help
"oj" wrote:

> Here is some info to get you started.
> select [IPA number (from the IPA_Num field)]=IPA_Num,
> [Total number of Rx's (sum of rx field )]=sum(rx),
> [Total Paid( sum of amt_paid field)]=sum(amt_paid),
> [Total generic paid (If the field drugs = 1 then sum the amt_paid
> field)]=sum(case when drugs=1 then amt_paid else 0 end),
> [Percent generic (If the field drugs = 1 then sum and divide by the sum of
> the field rx)]=sum(case when drugs=1 then amt_paid else 0 end)/sum(rx),
> [Percent formulary (if the formulary field = A then sum and divide by the
> rx field)]=sum(case when formulary='A' then amt_paid else 0 end)/sum(rx),
> [Cost difference ( Average amount paid per brand Rx - average amount
> paid per generic Rx )]=Avg(rx)-Avg(case when drugs=1 then amt_paid else 0
> end)
> from your_table
> group by IPA_Num
> Btw, I suggest you post DDL (create table) + sample data (insert) + expect
ed
> output here. It would be much easier for us to help you.
>
> --
> -oj
>
> "HasanaMonique" <HasanaMonique@.discussions.microsoft.com> wrote in message
> news:2D71E579-4920-4366-A09F-A66013FCE824@.microsoft.com...
>
>