CODEWITHSUNDEEP
sql-servert-sql
Padmanabhan Viswanathan
Padmanabhan Viswanathan

Reputation: 51

Better way to write a script

I have the following information for diamond stones sold in various regions.

Temp1

Region Cut Color SoldQty 
------- -- ----- ------- 
01      RD   C     1
01      RD   A     1 
01      RD   C     3
01      BA   C     2
02      RD   A     2
02      BA   A     3
02      BA   B     0
02      BA   A     1

From the above, I need to get the following information in SQL Server 2005. Basically, for a unique combination of Region, Cut and Color, I need Sold Qty summed up and the % sold calculated for every region.

Final output needed

Region Cut Color SoldQty TotalSOld  %SOld
------- -- ----- ------- ---------  -----
01      RD   C     1       4         4/7
01      RD   A     1       1         1/7
01      BA   C     2       2         2/7
02      RD   A     2       2         2/6
02      BA   A     4       4         4/6
02      BA   B     0       0          0

In order to accomplish this I used 2-3 temp tables - as follows.

select 
    Region, Cut, Color, 
    sum(SoldQty), 
    'TotalSoldQty' =  Sum (SoldQty) OVER(PARTITION BY Region) 
into temp2 
from temp1 
group by Region, Cut, Color

This would give table temp2 as below.

Region Cut Color SoldQty TotalSOld  
------- -- ----- ------- ---------  
01      RD   C     1       4         
01      RD   A     1       1         
01      BA   C     2       2         
02      RD   A     2       2         
02      BA   A     4       4         
02      BA   B     0       0

Then I added another select as below to get the final table.

select 
    Region, Cut, Color,SoldQty, TotalSOld,  
    'PercentageSoldQty' = case when TotalSold = 0 then 0 
                               else (SoldQty *100/TotalSold)  end
from temp2

The above gives me the result, but I am sure, there must be a better way to accomplish this within a single select than using multiple temp tables.

Can anyone help?

Upvotes: 1

Views: 89

Answers (2)

aquinas
aquinas

Reputation: 23796

How do you end up with SoldQty and TotalSold being different in row 1? That seems like a mistake to me. How about this:

http://www.sqlfiddle.com/#!6/0f5fe/24

select region,cut,color, soldqty, cast(soldqty as varchar) +'/' + cast(regiontotal as varchar) PercentSold FROM (
  select region,cut,color, sum(soldqty) soldqty, regiontotal from (
    select soldqty, region,cut,color,sum(soldqty) over (partition by region) as regiontotal
    from sale
  ) b
  group by region,cut,color, regiontotal
)foo

Upvotes: 1

Woot4Moo
Woot4Moo

Reputation: 24326

This is an instance where primary keys (or candidate keys I believe as they are known) come in handy. IF you modify your structure from: 

Region Cut Color SoldQty

to 

ID Region Cut Color SoldQty

you can now look up by ID. Which also gives you the ability to do this: 

select Region,Cut,Color, sum(SoldQty), 
'TotalSoldQty' =  Sum (SoldQty) OVER(PARTITION BY Region)

without any temp tables being generated.

Upvotes: 0

Related Questions