#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Sum(Price * quantity) not returning in 1 row.


    Code:
    Table PartsOrders
    Job   Desc   Price   Quantity
    5    Motor    $100   1
    5    Bearing  $20     2
    5    Stabilizer  $10   1

    I'm trying to get the query to display the total cost of this job.

    Code:
    SELECT Job, Sum(Price * Quantity) AS TotalCost FROM PartsOrders

    I'm expecting the result to be;
    Code:
    5, $150
    However the result is;
    Code:
    5, $110
    5, $40
    And it doesn't put them in 1 row. Any ideas?
    (Note: I've severely reduced the query for ease of viewing, full query is below)

    Code:
    SELECT ServiceJobCards.Job_Number, ServiceJobCards.Date_In, ServiceJobCards.Work_Done_Date, ServiceJobCards.DeliveryType, ServiceJobCards.ServiceType, ServiceJobCards.CustomerID, ServiceJobCards.ModelNumber, Customers.[First Name], Customers.Surname, Sum([ServicePartsOrders]![Unit Price]*[ServicePartsOrders]![Quantity]) AS Expr1, ServiceJobCards.Labour FROM (ServiceJobCards INNER JOIN Customers ON ServiceJobCards.CustomerID = Customers.CustomerID) INNER JOIN ServicePartsOrders ON ServiceJobCards.Job_Number = ServicePartsOrders.CustomerOrderID GROUP BY ServiceJobCards.Job_Number, ServiceJobCards.Date_In, ServiceJobCards.Work_Done_Date, ServiceJobCards.DeliveryType, ServiceJobCards.ServiceType, ServiceJobCards.CustomerID, ServiceJobCards.ModelNumber, Customers.[First Name], Customers.Surname, ServiceJobCards.Labour, ServicePartsOrders.Quantity, ServiceJobCards.Status, ServiceJobCards.Paid HAVING (((ServiceJobCards.Status)=1) AND ((ServiceJobCards.Paid)=2));
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    first, you're obviously using msaccess, not mysql, so i've moved your question out of the mysql forum

    your problem is due to the fact that you have ServicePartsOrders.Quantity in the GROUP BY clause, when you're also using it in the SUM aggregate

    as well, you have a couple extra columns in your GROUP BY clause happen to be the columns in your HAVING clause -- i've seen this nonsense before, it looks like you've used the graphic query builder in msaccess because msaccess generated the HAVING clause when it should've used a WHERE clause instead

    try this --
    Code:
    SELECT ServiceJobCards.Job_Number
         , ServiceJobCards.Date_In
         , ServiceJobCards.Work_Done_Date
         , ServiceJobCards.DeliveryType
         , ServiceJobCards.ServiceType
         , ServiceJobCards.CustomerID
         , ServiceJobCards.ModelNumber
         , Customers.[First Name]
         , Customers.Surname
         , SUM([ServicePartsOrders]![Unit Price] *
               [ServicePartsOrders]![Quantity] ) AS Expr1
         , ServiceJobCards.Labour 
      FROM (
           ServiceJobCards 
    INNER 
      JOIN Customers 
        ON Customers.CustomerID = ServiceJobCards.CustomerID
           ) 
    INNER 
      JOIN ServicePartsOrders 
        ON ServicePartsOrders.CustomerOrderID = ServiceJobCards.Job_Number
     WHERE ServiceJobCards.Status = 1
       AND ServiceJobCards.Paid = 2
    GROUP 
        BY ServiceJobCards.Job_Number
         , ServiceJobCards.Date_In
         , ServiceJobCards.Work_Done_Date
         , ServiceJobCards.DeliveryType
         , ServiceJobCards.ServiceType
         , ServiceJobCards.CustomerID
         , ServiceJobCards.ModelNumber
         , Customers.[First Name]
         , Customers.Surname
         , ServiceJobCards.Labour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0
    Thanks r937.

    Yes, I tried using the auto builder as it was previously an easy query but started getting incredibly complex as the database got bigger.

    At a quick glance, it seems to have worked, however.. there are some Jobs where the parts cost is $0. Instead of showing $0 the row is just not displayed at all. Any idea how to resolve this?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Mr Big
    Any idea how to resolve this?
    examine the data carefully

    it will be either an incomplete join or one of the WHERE conditions is not met
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo