We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

chrispaton
chrispaton asked
on
Medium Priority
496 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Commented:
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

Commented:
Try adding the condition to the JOIN itself

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

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

Commented:
My pleasure
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.