Sum Query

[Guest]

My table name is trade - it has 5,000,000 records. I would like all fields in
the table to be displayed in the qury result.

For each Date and c=Customer, I need to sum Qty.

I think this is easy but cannot get it to work.

Thank you in advance.

 

[Guest]

SELECT Trade.Customer,
Trade.[Date],
Sum(Trade.Qty) AS SumOfQty
FROM Trade
GROUP BY Trade.Customer, Trade.[Date],
ORDER BY Trade.Customer, Trade.[Date];

You may need to plug in the correct table and field names.

Also if the Date field include time, or it isn't an actual Date/Time
datatype, you may get some strange results.

With 5 million records, it could take a long time to run. Removing the Order
By clause could speed things up. Also having the combination of Customer and
Date fields indexed would help.

 

[Marshall Barton]

My table name is trade - it has 5,000,000 records. I would like all fields in
the table to be displayed in the qury result.

For each Date and c=Customer, I need to sum Qty.

It doesn't make much sense to display all fields and at the
same time calculate a sum of a field. Calculating an
aggregate value such as sum means that you have to specify
the fields that determine the grouping used to total.
Grouping on all fields almost always means that your sum
will only be for a single record, which is a waste of time.

To calculate a total for each customer for each date:

SELECT customer, datefield, Sum(Qty) As Total
FROM table
GROUP BY customer, datefield