SUM without GroupBy

[jsdba]

Hi experts,

I need a solution to retrieve records using SUM and GroupBy.
My problem:

I have two tables, refer to attachment of database and/or picture. SQL Code below also. I first need to aggregate the EstimateAmount in tblTaskCheckEstimate and GroupBY TaskID. Then there is two where clause in the query.

• First the aggregated estimate amount need to be more than 200
• The aggregated amount need to include and record where Final Estimate Amount = False

If i add the field FinalEstimateApprove to the SQL then i need to GroupBY, but sometimes the aggregate will include both FinalEstimateApprove = false and true. Then my aggregate is now group by false vs true. But i still need to find records > 200. How can i do this???:banghead::banghead::banghead:

SELECT tblTask.TaskID, Sum(tblTaskCheckEstimate.EstimateAmount) AS SumOfEstimateAmount, tblTaskCheckEstimate.FinalEstimateApprove
FROM tblTask INNER JOIN tblTaskCheckEstimate ON tblTask.TaskID = tblTaskCheckEstimate.TaskID
GROUP BY tblTask.TaskID, [B]tblTaskCheckEstimate.FinalEstimateApprove[/B]
HAVING (((Sum(tblTaskCheckEstimate.EstimateAmount))>200) AND ((tblTaskCheckEstimate.FinalEstimateApprove)=False));

I'm hoping this makes sense to someone. Let me know if more clarification of the problem is needed.

Thanks

 

[plog]

If i add the field FinalEstimateApprove to the SQL then i need to GroupBY

Huh?

You need to bring in every field you need into the query and select the proper 'Total:' method to handle it.

If you don't need to group by a field, but you do need to use it as criteria, then you would select 'Where'. If you want to add it all together you choose 'Sum', to count the values 'Count', etc.

I don't fully understand your issue, but I'm certain it has to do with your misunderstanding of what option to choose for the 'Total:' value of each field in your query.

 

[jsdba]

Plog thanks for your rely but please take a look at my attachments. I know i need to total i.e. sum and i have two criteria in my where clause. one criteria is messing up the records i get back. Let me put it in a practical example.

>I have a task.
>The task have estimates (dollar amounts), can be one can by many
>If the sum of the estimates exceeds 200 then i need retrieve the task.
>The estimates are individually approved by someone
> I only want to see the task if there is an estimate in the sum of estimates that has not been approved

E.G if there are two estimates first estimate for 205, then i need to see the task and approve the estimate. Given that I've approved the estimate i don't want to see the task anymore. Now then a second estimate comes in for 100 now the total estimate is 305. I want to see the task again because 1. the total exceeds 200 2. there is an estimate from the total that has not been approved.

The problem: If i use the criteria approved = false so i do select task where all estimates have been approved then i wont be able to see tasks that have estimates where have not yet been approved. Also adding the criteria "approved" to the query will automatically group-by said field...therefore separating approved vs not approved estimates...which messes up the accuracy of the aggregate.

Hope i was able to explain my dilemma a little better.

 

[plog]

The best explaination would be with data. Can you post another database with more data in your tables. Then show me what data you expect your query to return based on that data in your tables.

 

[jsdba]

Added a few more records.

I need to select task that has estimate amounts totaling > 200 IF the total has an estimate that has not been approve. i.e. FinalEstimateApprove = True.

One task has many estimates. If there is an estimate that has not been approved and the total of those estimates > 200 (groupby taskID) then i want to select that taskID.

 

[plog]

Ok, now using that data, what data do you expect your query to return? Don't explain to me, show me.

 

[jsdba]

Ok, now using that data, what data do you expect your query to return? Don't explain to me, show me.

I'm not sure what u mean "show me".

 

[plog]

You posted starting data in your last post. Post another set of data that shows what your query should return.

 

[Brianwarnock]

Not sure I fully understand but I think that you need to differentiate Where and Having clauses
I think that your check for true/false needs to be in a Where clause, this takes effect before aggregation and is created in the design grid by selecting Where from the Total menu, the Show box will be deselected.
Having check is done after aggregation.
Hope this helps.

Brian

 

[jsdba]

Brian that didnt work. Got the same results. See SQL:

SELECT tblTask.TaskID, Sum(tblTaskCheckEstimate.EstimateAmount) AS SumOfEstimateAmount
FROM tblTask INNER JOIN tblTaskCheckEstimate ON tblTask.TaskID = tblTaskCheckEstimate.TaskID
WHERE (((tblTaskCheckEstimate.FinalEstimateApprove)=False))
GROUP BY tblTask.TaskID
HAVING (((Sum(tblTaskCheckEstimate.EstimateAmount))>200));

Plog: Query1 in the attachment is all i need from the query. TaskID and SumofEstimateAmount. But its not correct because i should get the following

TaskID| SumofEstimateAmount
102 250
103 205

102 because EstimateID 29 has not been approved (FinalEstimateApprove)
103 because EstimateID 31 has not been approved (FinalEstimateApprove)

 

[plog]

Change the 'Where' underneath FinalEstimateApprove to 'Max'. That will get you the results you want for the data you have provided.

And just because its serving no real purpose, you don't need tblTask in that query at all. Simply use the TaskId from the estimate table.

 

[jsdba]

Change the 'Where' underneath FinalEstimateApprove to 'Max'. That will get you the results you want for the data you have provided.

And just because its serving no real purpose, you don't need tblTask in that query at all. Simply use the TaskId from the estimate table.

Plog you're the MAAAAAAAAAAAAAAAAAA!!!!!!!!!! I'm not sure why that works though. Please enlighten me.

 

[plog]

Oh it works--it produces the results you wanted, qed. Now, if it's the logic you are actually after for your entire database, well that's a different story. Let me do some explaining now:

That query returns all tasks where the sum of its EstimateAmount values are greater than 200 and it has at least one record with a False FinalEstimateApprove value.

If that's not what you are after. Please explain your desired logic in one simple sentence.

 

[jsdba]

Oh it works--it produces the results you wanted, qed. Now, if it's the logic you are actually after for your entire database, well that's a different story. Let me do some explaining now:

That query returns all tasks where the sum of its EstimateAmount values are greater than 200 and it has at least one record with a False FinalEstimateApprove value.

If that's not what you are after. Please explain your desired logic in one simple sentence.

This is EXACTLY what i wanted to do!! Thank you sir!!! But i was curious as to why/how 'Max' did the trick.

 

[plog]

Oh ok, thought you were questioning if it would work for the larger dataset.

0=False and -1=True. When you take the maximum of a True/False field, if they are all True the maximum will be -1 (because they are all -1). If just 1 False is in there, the maximum becomes 0.

So when you used that instead of WHERE you were able to apply criteria to the whole dataset, not just individual records (which is what WHERE does).