0

I'm looking to export a csv file from the data from my 2 tables 'Order_Headers' and 'Order_Lines' that will look like this

ORDH 10061 0 07/07/14 Bob Davies 32 Somewhere street etc
ORDL 10061 1 Product one Price etc
ORDL 10061 2 Product one Price etc
ORDL 10061 3 Product one Price etc
ORDH 10062 0 07/07/14 Peter Williams 45 Somewhere else road etc
ORDL 10062 1 Product one Price etc
ORDL 10062 2 Product one Price etc
ORDL 10062 3 Product one Price etc
ORDL 10062 4 Product one Price etc

The part I'm stuck with is the 3rd column, how do I count the number of items with each order line and enter the number into my 3rd column in MySQL database table?
As you can see with each new order the count needs to go back to zero as I'm only looking to count and record the number of order lines per invoice number.

user3616818
  • 1
  • 1
  • I would wrap the MySQL within a scripting language - e.g. PHP - The 1st SELECT to pull the customer info then another query to pull each product. Loop by customer. Good luck. – user3741598 Jul 03 '14 at 19:43
  • You can order the rows for each order by a specific column, say product_id, and then calculate the rank within each order as a group. Check this related question - **How to perform grouped ranking in MySQL** (http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql) – Joseph B Jul 04 '14 at 01:22

0 Answers0