Share of sales %

• anbu

  SSC-Addicted

  Points: 407

  More actions

  October 16, 2002 at 1:24 am

  #159178

  Hello all,

  I want to find the share of sales % for each sales item.

  Fox example, Sales item can be a crate of 2 pepsi bottles and Item can be a single pepsi bottle. A single Pepsi bottle can have sub levels like the label, cap , and the actual drink inside. I want to know the the share of sales % for each of the item for a particular sales item. (Forgetting the third level). First 2 levels are enough. For example 1 crate contains 2 bottles. So each bottle contributes 50% of the actual sale.

  Please reply me ASAP, if you come out with any clue.

  Thanks and regards,

  Anbu

• NPeeters

  SSChampion

  Points: 12581

  More actions

  October 16, 2002 at 2:16 am

  #438644

  Can you give us more information on how your database is organised?

• anbu

  SSC-Addicted

  Points: 407

  More actions

  October 16, 2002 at 3:24 am

  #438647

  sales_item Item Qty

  01 05 2

  01 06 4

  01 07 6

  Hence share of % for each Item would be

  Item 05 : 16.67

  06: 33.33

  07: 50.00

• Antares686

  SSC Guru

  Points: 125444

  More actions

  October 16, 2002 at 3:51 am

  #438648

  This should do the trick.

  SELECT

  salesx.sales_item,

  salesx.Item,

  salesx.Qty,

  CAST(((salesx.Qty / TSPI.TotQty) * 100) AS NUMERIC(10,2)) As Prcnt

  FROM salesx

  INNER JOIN

  (SELECT sales_item, CAST(sum(Qty) as NUMERIC(10,5)) TotQty FROM salesx GROUP BY sales_item) as TSPI

  ON

  TSPI.sales_item = salesx.sales_item

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• anbu

  SSC-Addicted

  Points: 407

  More actions

  October 16, 2002 at 4:33 am

  #438652

  Thanks Andares, It works!

  Thanks and regards,

  Anbu