Executing SQL Query with conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Executing SQL Query with conditions

Author

Topic

challapavan
Starting Member

8 Posts

Posted - 2007-08-22 : 10:12:29

I have a Columns like

Region Amount

Asia 5600
Asia 6500
Asia 1000
US 8900
US 4400
US 6700
Europe 4500
Europe 2400
Europe 1600
Africa 6200
Africa 6400
Africa 6900

i want to execute a query with conditions...like

if Region==Asia then compute percent of Amount
if Region==US then compute percent of Amount
if Region==Europe then compute percent of Amount
if Region==Africa then compute percent of Amount

the result set to be displayed something like

Column---Amount---PercentAmount

thanks a lot!

pavan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-22 : 10:22:09

can you show us how is your expected output like ?

KH
[spoiler]Time is always against us[/spoiler]

challapavan
Starting Member

8 Posts

Posted - 2007-08-22 : 10:36:13

The Out put is something like it has to display with the following columns.....

Region---Amount---PercentAmount

pavan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-22 : 10:41:16

i mean with the sample data you provided, what is output of the required query ?

And what do you mean by this ?

quote:
f Region==Asia then compute percent of Amount
if Region==US then compute percent of Amount
if Region==Europe then compute percent of Amount
if Region==Africa then compute percent of Amount

KH
[spoiler]Time is always against us[/spoiler]

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-22 : 11:32:01

Maybe:

SELECT D1.Region
    ,D1.Amount
    ,ROUND(D1.Amount * 100.0 / D2.Total, 0) AS PercentAmount 
FROM (
        SELECT Region, SUM(T.Amount) AS Amount
        FROM YourTable T
        GROUP BY T.Region
    ) D1
    CROSS JOIN (
            SELECT SUM(T1.Amount) AS Total
            FROM YourTable T1
        ) D2

sbalaji
Starting Member

48 Posts

Posted - 2007-08-23 : 01:18:54

My way of doing it,

select Region ,sum(amount)'Amount', round(sum(amount)/b.Gtotal * 100,2) 'Percentage'
from [Tablename],
(select sum(amount) 'Gtotal'
from [Tablename]) b
group by Region,Gtotal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources