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
