Solved Query Group by (1 Viewer)

[mansied]

hello,
I have a quay to group by 3 field and do sum in other fields .
I want to have two other fields but not to be group by I want to be a part of table but i face the below error.
how can do that ? any idea ?
I appreciate for your help

 

[theDBguy]

To add the additional column without grouping them, use a separate query to join the group by query with your table again.

Hope that helps...

 

[Pat Hartman]

ALL fields in a totals query MUST be grouped by or aggregated using some other function. To have non-aggregated fields prevents the data from being aggregated.

theDBguy's suggestion might not help because you may not have any way of joining back to the original table to pick up the other fields because they would have been summarized away to create the totals.

Give us a business use case description of what you are trying to do and we'll offer an alternative. You seem to be summing some field and you are trying to include a uniqueID. That does not compute as we say. If the sum is for 10 records, there will be 10 IDs and if you include the ID, then there will be no summing. Each row will be returned based on the the uniqueness of all the fields being grouped by.

If the first three fields are sufficient to join back to the table to pick up the ID's as theDBguy suggested, you will get 10 rows if there are 10 records summarized and each row will have the same value for the sum field.

 

[plog]

First, action queries (UPDATE, INSERT, DELETE, etc.) are a red flag of a poor database. Another red flag is storing a calculated value (SUM()). So, I'm really interested in what the purpose of this query is? Why must you move data and not use a query to obtain the data you want?

Then for your specific question--data issues are best communicated with sample data. Please provide us 2 sets of sample data:

A. Starting data from DA_Converted. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with after running the data from A through your query.

Again, 2 sets of data that demonstrate the before and after of this query.

 

[Pat Hartman]

I didn't even notice it was an update query. I guess that would be the next error message once the fields are fixed