I just started messing with our monthly sales reports and how they get collected is in CVS file by weekly sales, then a total for the month.
The monthly total CSV has the following schematic:
TITLE | BOOKID | SALES QTY | SALES $
Because the data seems like it is generated every few days, the titles are duplicated in the CSV file across the month for example:
Book1 | 001 | 12 | $340
Book2 | 002 | 10 | $260
Book1 | 001 | 8 | $240
Book5 | 005 | 2 | $60
How do I make it so that, if last months report has the same title mentioned 3 times, I can merge those rows AND update the total sales QTY and sales $ amounts into a single row?
In essence, it ends up looking like this:
Book1 | 001 | 20 | $580
Book2 | 002 | 10 | $260
Book5 | 005 | 2 | $60
Is this possible to do with an SQL query or is it simpler to do using Excel (if so how? LOL) and just reupload a 'clean' CSV to the database?
