One line -> several outputs/ Join tables

[Tiidi]

Hey there

I try to find how many times a person did order and what is the day difference between it (second step):

Table is called
eshop_flat_sales_order

customer_email created_at status
a(at)a.com 12.1.10 complete
b(at)a.com 14.2.10 cancelled
c(at)a.com 16.1.10 complete
a(at)a.com 18.1.10 complete
c(at)a.com 18.1.10 complete
b(at)a.com 20.1.10 complete

Now I want that SQL shows me the a(at)a.com person and all the dates this person ordered with the status complete. So for a(at)a.com I want the output 12.1 and 18.1.

If i put:

SELECT customer_email, created_at
FROM *
WHERE 'status' = 'complete'
Group by customer_email

It only shows me the first created_at date because I know its only a single row.But is there a way it shows me all the date in more than one row? In a second step I do wana callculate the difference between the first and the following orders but I think Ill use excel afterwards. Or is there a callculation in MySql?

Thanks very much, i just getting started with mysql and its quit a univers for me

[StephNicolaou]

I got the query to work using this statement:

SELECT *
FROM eshop_flat_sales_order
WHERE (((status)="complete") AND ((customer_email)="a(at)a.com"));

You would have to replace "a(at)a.com" as a variable name input from your front end, along with "complete" if you would like the user to select the status as well.

In order to get the number of days between two dates, you would need to use the DATEDIFF(date1, date2) method.

However, as your dates you want to compare are in different rows and not two dates in one row, (as shown in examples), you have to use this subquery statement:

SELECT id,customer_email,  date,   
DATEDIFF((SELECT MAX(date) FROM eshop_flat_sales_order WHERE date < a.date),     date) AS days_since_last FROM atable AS a

Resource used:
http://stackoverflow.com/questions/7375373/select-difference-between-row-dates-in-mysql

Hope that helps :)

[Tiidi]

Thank you so much. Now I get the line for a(at). Is it possible to get it for all? There are over 9000 customers I have to sort :/. The output I wish is:

a(at)a.com 12.1.10 complete
18.1.10 complete
b(at)a.com 14.2.10 cancelled
20.1.20 complete
c(at)a.com 16.1.10 complete
18.1.10 complete

Is that possible to get?

Thanks :)

[StephNicolaou]

Not directly in the database, the additional rows for the customer's other dates would be required - showing the customer email again and again for every date they have in each row.

This can be done in the front end when you get to coding/ managing your layout. I can't really say how right now with not knowing what language/ type front end you're using...

The SQL query needed, to see all customers (email's repeated for each date) would be to just select all and order by customer email:

SELECT *
FROM eshop_flat_sales_order
ORDER BY customer_email;

:)