Keran O'Brien
asked on
SQL using aggregate function with HAVING / WHERE clause.
Hi,
I have 2 tables involved in a Query:
Table of 40 customers (dbo_Top40_ECSTN)
Table of claims made by all Customers (dbo_Claims)
I am building a query which returns the customer name, counts the claims and sums the cost of those claims per each customer in the top 40 table:
SELECT dbo_Top40_ECSTN.CustomerNa me, Count(dbo_Claims.ClaimID) AS [No Of Claims], IIf(IsNull(Sum(dbo_Claims. CreditNote Value)),Su m(dbo_Clai ms.Account Value),Sum (dbo_Claim s.CreditNo teValue)) AS [Sum of Claims]
FROM dbo_Top40_ECSTN LEFT JOIN dbo_Claims ON dbo_Top40_ECSTN.CustomerNu mber = dbo_Claims.CustomerID
GROUP BY dbo_Top40_ECSTN.CustomerNa me, dbo_Top40_ECSTN.Rank, dbo_Claims.Status
HAVING dbo_Claims.Status<>"CANCEL LED" AND dbo_Claims.Status<>"NOT ACCEPTED"
ORDER BY dbo_Top40_ECSTN.Rank;
The above returns 48 records, I guess because of "dbo_Claims.Status" in GROUP BY so I think I understand this. customers with 0 claims are not included.
If i use WHERE instead of HAVING i get 29 records; it again does not include customers with 0 claims and it also does not group the claims by status. I also think i understand why it does this.
My question is how do i get it to perform the aggregates properly and include all records in the Top40 table regardless of whether there are claims from that customer?
Customer 1 2 £2100
Customer 2 0 £0
Customer 3 12 £10900
Thanks for any advice!
I have 2 tables involved in a Query:
Table of 40 customers (dbo_Top40_ECSTN)
Table of claims made by all Customers (dbo_Claims)
I am building a query which returns the customer name, counts the claims and sums the cost of those claims per each customer in the top 40 table:
SELECT dbo_Top40_ECSTN.CustomerNa
FROM dbo_Top40_ECSTN LEFT JOIN dbo_Claims ON dbo_Top40_ECSTN.CustomerNu
GROUP BY dbo_Top40_ECSTN.CustomerNa
HAVING dbo_Claims.Status<>"CANCEL
ORDER BY dbo_Top40_ECSTN.Rank;
The above returns 48 records, I guess because of "dbo_Claims.Status" in GROUP BY so I think I understand this. customers with 0 claims are not included.
If i use WHERE instead of HAVING i get 29 records; it again does not include customers with 0 claims and it also does not group the claims by status. I also think i understand why it does this.
My question is how do i get it to perform the aggregates properly and include all records in the Top40 table regardless of whether there are claims from that customer?
Customer 1 2 £2100
Customer 2 0 £0
Customer 3 12 £10900
Thanks for any advice!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both.
Just so i'm clear, @PatHartman, are you saying the solution that Ryan Chong proposed could produce unexpected results?
I have now run into another problem. After implementing Ryan's solution it worked fine, but then I needed to develop the query further. I needed to show the total order volume per customer also. So I made another left join to table dbo_Intake (dbo_Top40_ECSTN.CustomerN umber = dbo_Intake.CustomerID) and summed the order quantities for each of the customers. The result was 40 records but the claim field is now incorrect, showing many multiples of the correct number..i think it is repeating its sum for each order that that customer has made?
Just so i'm clear, @PatHartman, are you saying the solution that Ryan Chong proposed could produce unexpected results?
I have now run into another problem. After implementing Ryan's solution it worked fine, but then I needed to develop the query further. I needed to show the total order volume per customer also. So I made another left join to table dbo_Intake (dbo_Top40_ECSTN.CustomerN
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks!@PatHartman
In the meantime I messed around with what Ryan had posted so I understood fully, plus I did what you said - using a seperately saved query and between the two I now have the desired result.
question; when using a querydef that requires parameters in this way how do i pass them? (tell me if this should be a seperate question and ill post it up)
In the meantime I messed around with what Ryan had posted so I understood fully, plus I did what you said - using a seperately saved query and between the two I now have the desired result.
question; when using a querydef that requires parameters in this way how do i pass them? (tell me if this should be a seperate question and ill post it up)
ASKER
thanks guys, i think i should post up my other question seperately
It should be a separate question since you have closed this one already but I'll answer it anyway.
As long as the parameter name is the same in all parts of the query, Access will only prompt once. However, if there is a Crosstab in the mix, you will need to explicitly define the parameters in all the parts of the query. Apparently, the programmer who developed the Crosstab didn't use the same specification as the programmers who developed the other query types.
And finally, the best way to pass parameters is to use a form. Create either visible (if the user is doing the selection) or hidden (if the program will be providing the parameter values) textboxes, listboxes, checkboxes, or combos. Use a click event to run the form or report and before opening the form or report, validate the parameters.
As long as the parameter name is the same in all parts of the query, Access will only prompt once. However, if there is a Crosstab in the mix, you will need to explicitly define the parameters in all the parts of the query. Apparently, the programmer who developed the Crosstab didn't use the same specification as the programmers who developed the other query types.
And finally, the best way to pass parameters is to use a form. Create either visible (if the user is doing the selection) or hidden (if the program will be providing the parameter values) textboxes, listboxes, checkboxes, or combos. Use a click event to run the form or report and before opening the form or report, validate the parameters.
ASKER
Could you tell me what going on with this then?
Do i understand correctly to say in the JOIN statement instead of using the complete claims table we are using a "filtered" version?