Second Group By Not Working

[stvslayer]

I'm new to the forum and I have searched some of the posts, but I'm not quite finding my same problem.

I have a customer transaction table with the date, customer ID, and transaction amount listed. I'm trying to get a report that groups the transactions by the date and then totals the transactions for each customer on that date. The first grouping (Date) is working, but the customer ID keeps getting repeated on the same date when they have multiple entries. I thought the group by would eliminate the duplicates within the same date. Am I just misinterpreting this? When I run the grouping with just date or just customer ID, it works great. It's when I try and do both that the problem occurs. Here's my statement:

SELECT Customer.TxnDate, Customer.CustID, SUM(Customer.TxnAmount) AS Total
FROM Customer
GROUP BY Customer.TxnDate, Customer.CustID;

Any help would be appreciated. I'm hoping I'm just missing something really basic here.

 

[pbaldy]

My first guess was that the date field included a time, even if it was not immediately visible. However, that would mean it wouldn't work when you just grouped on the date field. Can you post the db here, or a sample that exhibits the problem?

 

[stvslayer]

Thanks for your thoughts! It was indeed storing the time, it just wasn't visible. I thought the grouping by date was working as we did have several transactions that were date/time stamped the same so it looked correct. I really should have thought of the time. But I'm glad it was something simple to fix. Thank you again for help!!

Thanks,
Steph

 

[pbaldy]

Happy to help, and welcome to the site by the way!