SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How can I overtake categorized data for doing operations

    Below table is my favorite result (please click on image to enlarge it) :

    I have problem with calculation of weightPercent column :
    In above table (20) is calculated as described below :
    (200/(200+300)) * (100/200) * 100 = 20
    So query should be something like :
    Code:
    SELECT   projectID
                  ,projectName
                  ,orderAmount
                  ,delivered
                  ,((orderAmount/(?)) * (delivered/orderAmount) * 100) AS weightPercent
    Group By projectName;
    I don`t know how should I interact with (?) mark in above query ? I need to calculate that summation for every project
    separately .
    P.S : Above query is a abridgement of one complicated query . I tested SUM(DISTINCT *) but I got many problems with it ,
    so I`m looking for any other solution ;Is there any other trick for it ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by omid020 View Post
    In above table (20) is calculated as described below :
    (200/(200+300)) * (100/200) * 100 = 20
    what are those numbers? where do they come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have placed one screenshot that shows the final result; those numbers are description of how (20) is obtained from first row of that table.That is just one example to show how table should work to calculate "weightPercent".

  4. #4
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (200--->from orderAmount/(200+300)--->from orderAmount) * (100---->from delivered/200---->from orderAmount) * 100 = 20

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT daTable.projectID
         , daTable.projectName
         , daTable.orderAmount
         , daTable.delivered
         , daTable.delivered * 100.0 /
              subtotals.project_amount AS weightPercent
      FROM ( SELECT projectName
                  , SUM(orderAmount) AS project_amount
               FROM daTable
             GROUP
                 BY projectName ) AS subtotals
    INNER
      JOIN daTable
        ON daTable.projectName = subtotals.projectName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting solution , TNX


Bookmarks

Posting Permissions

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