Sample Header Ad - 728x90

MySQL Conditional Counter Based Other Columns?

0 votes
1 answer
724 views
I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1. ---------------------------------------------------------------- | Customer Name | Product | PurchaseDate | PurchaseNumberForDate | ---------------------------------------------------------------- | Customer A | ... | 2019-04-01 | 1 | | Customer A | ... | 2019-04-02 | 1 | | Customer A | ... | 2019-04-03 | 1 | | Customer A | ... | 2019-04-03 | 2 | | Customer A | ... | 2019-04-03 | 3 | | Customer B | ... | 2019-04-03 | 1 | | Customer B | ... | 2019-04-03 | 2 | | Customer B | ... | 2019-04-04 | 1 | ---------------------------------------------------------------- I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate correctly calculated, I will use this as a subquery with another query that will select WHERE PurchaseNumberForDate = 1. I have found plenty of examples using COUNT() and @var := @var+1, but I haven't found one based on multiple conditions. Is this possible with MySQL?
Asked by jimp (163 rep)
Apr 3, 2019, 08:42 PM
Last activity: Jun 3, 2025, 04:06 AM