Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

SQL Query - SUM results with additional where conditions on joined table

I have a query setup to display results in a asp.net GridView, I an stuck with getting the SUM(Purchase_Orders.Price) to calculate properly as I want to add an additional condition to the formula. I want to filter out Purchase Orders that I have cancelld by means of a PO_Cancelled True/False Field.
I have tried changing the WHERE statement to 'WHERE        (JobNumbers.Job_Cancelled = 0) AND (Purchase_Orders.PO_Cancelled = 0) which does filter the query results and filters the sum correctly but it also filters out and jobs that do not have any Purchase Orders raised.

I have also tried adding a nested select statement to filter and sum the purchase orders but it only sums all of the results from the table and does not sum for the individual rows.

Thanks for your assistance


Chris
SELECT        JobNumbers.Date, JobNumbers.Job, JobNumbers.Customer, JobNumbers.QuotePrice, JobNumbers.Origin, JobNumbers.QuoteNumber, 
                         JobNumbers.Invoiced, JobNumbers.PurchaseCosts, { fn IFNULL(SUM(Purchase_Orders.Price), 0) } AS Sum_Price, JobNumbers.Part_Invoice_Value, 
                         JobNumbers.Job_Completed, JobNumbers.Job_Finished, (JobNumbers.QuotePrice - SUM(Purchase_Orders.Price)) / JobNumbers.QuotePrice AS Profit, 
                         JobNumbers.Job_Cancelled
FROM            JobNumbers LEFT OUTER JOIN
                         Purchase_Orders ON JobNumbers.Job = Purchase_Orders.Job_Number
WHERE        (JobNumbers.Job_Cancelled = 0)
GROUP BY JobNumbers.Date, JobNumbers.Job, JobNumbers.Customer, JobNumbers.QuotePrice, JobNumbers.Origin, JobNumbers.QuoteNumber, 
                         JobNumbers.SageProject, JobNumbers.Invoiced, JobNumbers.PurchaseCosts, JobNumbers.Part_Invoice_Value, JobNumbers.Job_Completed, 
                         JobNumbers.Job_Finished, JobNumbers.Job_Cancelled
ORDER BY JobNumbers.Job DESC

Open in new window

0
chrispaton
Asked:
chrispaton
  • 2
  • 2
1 Solution
 
reb73Commented:
Try -
SELECT        JobNumbers.Date, JobNumbers.Job, JobNumbers.Customer, JobNumbers.QuotePrice, JobNumbers.Origin, JobNumbers.QuoteNumber, 
                         JobNumbers.Invoiced, JobNumbers.PurchaseCosts
			 ,ISNULL(SUM(CASE WHEN (Purchase_Orders.PO_Cancelled = 0) THEN 0 ELSE Purchase_Orders.Price END), 0) AS Sum_Price
			 ,JobNumbers.Part_Invoice_Value,JobNumbers.Job_Completed, JobNumbers.Job_Finished 
			 ,(JobNumbers.QuotePrice - ISNULL(SUM(CASE WHEN (Purchase_Orders.PO_Cancelled = 0) THEN 0 ELSE Purchase_Orders.Price END),0)) / JobNumbers.QuotePrice AS Profit, 
                         JobNumbers.Job_Cancelled
FROM            JobNumbers LEFT OUTER JOIN
                         Purchase_Orders ON JobNumbers.Job = Purchase_Orders.Job_Number
WHERE        (JobNumbers.Job_Cancelled = 0)
GROUP BY JobNumbers.Date, JobNumbers.Job, JobNumbers.Customer, JobNumbers.QuotePrice, JobNumbers.Origin, JobNumbers.QuoteNumber, 
                         JobNumbers.SageProject, JobNumbers.Invoiced, JobNumbers.PurchaseCosts, JobNumbers.Part_Invoice_Value, JobNumbers.Job_Completed, 
                         JobNumbers.Job_Finished, JobNumbers.Job_Cancelled
ORDER BY JobNumbers.Job DESC

Open in new window

0
 
openshacCommented:
Try adding the condition to the JOIN itself
0
 
chrispatonAuthor Commented:
Worked a treat, I had to change the PO_Cancelled = 0 to PO_Cancelled = 1 to get the value correct.
 
Thanks for you quick and helpfull response.
 
 
Chris
0
 
chrispatonAuthor Commented:
Worked a treat, I had to change the PO_Cancelled = 0 to PO_Cancelled = 1 to get the value correct.



Thanks for you quick and helpfull response.





Chris
0
 
openshacCommented:
My pleasure
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now