Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post

    Creating a query to assign percentage - MSSQL 2005

    Hi all,
    I have a table as below

    SalesPerson_ID | Product_ID | Price_DC
    4233 1 500.00
    4233 1 1200.00
    4233 2 50.00

    4234 1 200.00
    4234 1 200.00
    4234 3 600

    I want to find that for Sales Person - 4233, the percentage of sales he has done for the total sales of Rs 1750/-
    for eg in the above case his 1750/- comprises of 97% for product 1 and remaining 3% for product 2
    And for Sales Person 4234, for the total sales of 1000, the percentage for product 1 is 40% and for product 3 its remaining 60%

    How can i do this in a query
    is that possible?

    Thanks in advance

  • #2
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post
    SELECT distinct(SalesPerson_ID) , Product_ID , round((ShareOfProduct/TotalOfAll) * 100 ,0)AS 'Percentage'
    FROM
    (

    SELECT SalesPerson_id, DefaultAmount_DC , Product_ID ,
    SUM(DefaultAmount_DC) OVER () AS 'TotalOfAll',
    SUM(DefaultAmount_DC) OVER (partition BY SalesPerson_ID, Product_ID) AS 'ShareOfProduct'
    FROM Product_ContractingItems_T

    ) abc
    ORDER BY 'Percentage' DESC, SalesPerson_ID;

    Works wonders
    Hope this helps some one
    Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •