'Group By' & 'Sum' returning different totals

[machumpion]

Im running a query of customer sales in a particular period

A customer can have multiple orders in one period (one order per product)

i set the Date field criteria >#7/30/16#

I set Sales field = Group By to view the individual order $$s

when I go back to query design and only change the Sales = Sum, so i can view the total value of orders in that period, the total sales of each customer is different than when adding each individual order under the 'group by' query. In particular, it looks like in the 'sum' query, individual orders were multiplied by 2.

Why is this happening?

Thanks!

 

[plog]

Because you are Summing in one and Grouping in another. Read up on aggregate queries (http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial/) and the GROUP BY clause (http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial/)

GROUP BY tells the computer to condense down all the records with the same values in these fields to one record in the query.

YourTable
Field1, Field2, Field3
John, 1/1/2016, 17
John, 1/1/2016, 20
Sally, 1/1/2016, 91
Jim, 2/1/2016, 23
Jim, 2/2/2016, 30

SELECT Field1 FROM YourTable GROUP BY Field1
--will return the 3 records (the unique values in Field1-->Sally, Jim, John)

SELECT Field1, Field2 FROM YourTable GROUP BY Field1, Field2
--will return 4 records (every unique Field1/Field2 permutation)
--since John's records are the same for Field1 and Field2 they become 1 record in the query

SELECT SUM(Field3) FROM YourTable
--returns 1 record (181)
--no GROUP BY, so it condenses everything down

SELECT FIELD1, SUM(Field3) FROM YourTable GROUP BY Field1
--returns 3 records each name with all their sales added together
--note that this GROUP BY is the same as the first example

So to conclude the lesson, GROUP BY controls how many records you get. It returns unique permutations of all the fields you list in it. 2 rookie mistakes:

1. Including the primary key in the GROUP BY. Since a primary key is unique for every record, you achieve nothing by using it in the GROUP BY--it cannot condense down because no 2 records will have the same value.

2. Thinking the SELECT effects the GROUP BY. They are completely independent. You could put a field in the GROUP BY and not the SELECT and you would see duplicate values, but behind the scenes you are condensing down on more fields than you are showing so you think you have duplicates.