help with complex SQL query

I need some help from prog. gurus with sql query.

I have a commodity table and I would like to pull information from it based on the conditions. I can write code to pull straight forward information from the table but I need help in writing query such that the later columns are based on the former columns.

To clear my question, I am writing the sample code below.
The column 4, 5 and 6 are based on column 1, 2, 3.
Column6 is column3/column2 (which is simple)
But column4 & 5 are too complex for me to write the code for. Column4 is the % of Quantity (cty_code)/(all countries) for that particular commodity. cty_code correspends to a country.

Table information:

[dbo].[2005exp](
[dom_or_for] [char](1) NOT NULL,
[commodity] [char](10) NOT NULL,
[cty_code] [char](4) NOT NULL,
[district] [char](2) NOT NULL,
[stat_month] [char](2) NOT NULL,
[cards_mo] [decimal](8, 0) NOT NULL,
[all_qy1_mo] [decimal](12, 0) NOT NULL,
[all_val_mo] [decimal](12, 0) NOT NULL
)

---

Table data snapshot:

Query1:
select top 10 commodity, sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where cty_code=5310 group by Commodity order by price desc

Output:

Commodity	Quantity	Price	Column4	Column5	Price/Quantity
8517305000	0	46307629
8517905000	0	11990255
3003100000	2268	2687905	0.35%	29.92%	1185.1437
8524990000	148	1815000
8471300000	2591	1673570
9802400000	0	1560247
9880004000	0	1197407
8802300080	1	1100000
3819000000	601192	899417
9802200000	0	811996

Query2: (based on the results of query 1 i.e., commodity)
select Sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp]
where commodity=3003100000

Output:

Quantity	Price
645261	8982928

2268 / 645261 = 0.35% Column 4

2687905 / 8982928 = 29.92% Column 5

Quantity/Price = Average (2687906/2268 = 1185.1437) i.e. column3/column2=Column6

The final output should like: (There are additional 2 columns in the table below but they are not required)

select a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as Price,

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b

where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b

where a.commodity=b.commodity))*100.00 ELSE NULL END as col4, CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as col5,

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as col6

from [2005exp] a

WHERE a.cty_code=5310

GROUP BY a.commodity

|||

Thank you so much. The code works perfectly. I really appreciate all your help.

I wanted to add another column 7 which is column5/column4. How can I save the column5 and Column4 values in 2 different variables and then perform the division ?

I tried to use just the alias names i.e col5/col4, but its not working.

I tried using the actual code for both columns and its working but its taking 4 minutes to execute the query. Its repeating the same steps twice. How is it done right way ?

Also, I was trying to delcare a variable for the table name but without any success. Who is it done ?

Use test
Go

Declare @.Country Int
Set @.Country=5310

select top 10 a.commodity as Commodity, c.descrip_1 as Description, c.quantity_1 as Unit,

sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Quantity) %],

CASE WHEN (select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S. Share(Value) %],

CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price],

CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b where a.commodity=b.commodity)<>0 THEN
(((sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b where a.commodity=b.commodity)))/((sum(a.all_qy1_mo)/(select Sum(b.all_qy1_mo) from [2005exp] b where a.commodity=b.commodity)))) ELSE NULL END as [Price Ratio]

from [2005exp] a inner join concord c on a.commodity=c.commodity

WHERE a.cty_code=@.Country
GROUP BY a.commodity, c.descrip_1, c.quantity_1 order by [Value] desc