SUM of TotalSellPrice, Grouped by JobID? – Beginner SQL Question

I’m looking to write a query that gives the YTD sales for each job within our system. We use Bistrack. Here is what I have:

select C.Shortname AS "Customer", J.JobReference, (OH.TotalSellPrice - OH.NonSalesAmount) AS "TotalSellPrice", CIL.Amount AS "Contract Invoice", CA.Address1, CA.City, CA.TaxAreaID, T.Name AS "Tax Area", CA.PostCode AS "ZIP Code", OH.DateTimeCreated, TotalSalesYTD, J.JobID, CA.CustomerAddressID, C.CustomerID, SaleType
from dbo.CustomerAddress AS CA WITH(NOLOCK)
left join TaxArea AS T WITH(NOLOCK) ON CA.TaxAreaID = T.TaxAreaID
left join Customer as C WITH(NOLOCK) ON CA.CustomerID = C.CustomerID
left join Job as J WITH(NOLOCK) ON CA.CustomerAddressID = J.CustomerAddressID
left join CustomerFinancial as CF WITH(NOLOCK) ON CA.CustomerID = CF.CustomerID
left join OrderHeader AS OH WITH(NOLOCK) ON CA.CustomerAddressID = OH.DeliveryAddressID
left join ContractInvoicingLine AS CIL WITH(NOLOCK) ON OH.ContractInvoicingLineID = CIL.ContractInvoicingLineID 
WHERE TotalSalesYTD <> 0 AND OH.DateTimeCreated > '2019-12-31' AND OH.OrderStatus = 8 AND OH.SaleType = 3
ORDER BY C.ShortName, JobReference

This displays a result where each job reference is shown multiple times for each order under that job. I’d like to combine the orders into a SUM for each job. So, for example, I want the blue column to be a single number that is just 3362.68 + 101.03 + 1642.86 + 1298.75.

My idea would be to do SUM(OH.TotalSellPrice – OH.NonSalesAmount) AS “TotalSellPrice”, but that doesn’t work, seemingly because my other columns are not in the function. I think this might be something simple to fix but I just started in SQL. I have gathered that partitioning could be used from browsing this site, but I have no idea how to apply it to my current query. Any help appreciated, thanks!

Answer

From the error message you noted in the comments above, I’m going to assume you’re using SQL Server as a database appliance.

In SQL Server, and most other databases, when you use SUM or any other aggregate functions you are required to add a GROUP BY clause that includes all non-aggregated columns from the SELECT clause of the query.

SELECT 
   C.Shortname AS "Customer"
    ,J.JobReference
    ,SUM(OH.TotalSellPrice - OH.NonSalesAmount) AS "TotalSellPrice"
    ,CIL.Amount AS "Contract Invoice"
    ,CA.Address1
    ,CA.City
    ,CA.TaxAreaID
    ,T.Name AS "Tax Area"
    ,CA.PostCode AS "ZIP Code"
    ,OH.DateTimeCreated
    ,TotalSalesYTD
    ,J.JobID
    ,CA.CustomerAddressID
    ,C.CustomerID
    ,SaleType
FROM dbo.CustomerAddress AS CA 
LEFT JOIN TaxArea AS T 
  ON CA.TaxAreaID = T.TaxAreaID
LEFT JOIN Customer AS C  
  ON CA.CustomerID = C.CustomerID
LEFT JOIN Job AS J  
  ON CA.CustomerAddressID = J.CustomerAddressID
LEFT JOIN CustomerFinancial AS CF  
  ON CA.CustomerID = CF.CustomerID
LEFT JOIN OrderHeader AS OH  
  ON CA.CustomerAddressID = OH.DeliveryAddressID
LEFT JOIN ContractInvoicingLine AS CIL  
  ON OH.ContractInvoicingLineID = CIL.ContractInvoicingLineID
WHERE TotalSalesYTD <> 0
    AND OH.DateTimeCreated > '2019-12-31'
    AND OH.OrderStatus = 8
    AND OH.SaleType = 3
GROUP BY
   C.Shortname
    ,J.JobReference
    ,CIL.Amount
    ,CA.Address1
    ,CA.City
    ,CA.TaxAreaID
    ,T.Name
    ,CA.PostCode
    ,OH.DateTimeCreated
    ,TotalSalesYTD
    ,J.JobID
    ,CA.CustomerAddressID
    ,C.CustomerID
    ,SaleType
ORDER BY 
   C.ShortName
    ,JobReference

Plus, have a look at these, and then make informed decisions.

Bad habits : Putting NOLOCK everywhere

Is NOLOCK Ever The Right Choice?