Post reply

Query to Calculate percentages

  • December 2, 2008 at 6:03 am

    #124706

    Can some one please show me how I would structure a SQL query that gives me the Percetage of Items are purchased.

    EX.

    Order 1 - Item A 10

    Order 2 - Item B 10

    Order 3 - Item C 30

    Order 4 - Item C 30 & Item A 20

    Order 5 - Item B 20 & Item A 10

    RESULTS :

    Item A - 30.77%

    Item B - 23.08%

    Item C - 46.15%

    I am having a hard time trying to figure out to group the Items from different orders to add them up and then divide by all items quantites purchased.

    Thanks

  • December 2, 2008 at 6:38 am

    #906184

    what does your table structure look like?

  • December 2, 2008 at 6:41 am

    #906186

    Concept:

    Write your query to link the original table grouped by item # to a derived table with a sum of all items. Divide the sum of the parent query specific product by the sum of all products(from your derived table) and multiply by 100 for your percent.

    If you want actual code, please refer to the link in my signature on how to post table structure and sample data.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 2, 2008 at 6:45 am

    #906191

    A very similar question was asked and answered here...http://www.sqlservercentral.com/Forums/Topic610366-8-1.aspx#bm610396

    Cheers

    chrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply