join tables or one line with several outputs...

[Trulla]

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

 

[OsakaWebbie]

If you just want the order dates on separate lines, just "ORDER BY customer_email" (not GROUP BY) will work. But if you are saying that you want the dates of multiple orders together in the same row, you can use the GROUP_CONCAT function, like this:

SELECT customer_email, GROUP_CONCAT(created_at ORDER BY created_at SEPARATOR ',') AS orders
FROM eshop_flat_sales_order
WHERE 'status' = 'complete'
GROUP BY customer_email

I used a comma as the separator in my example, but you can use any character or string you want. Read more at:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

 

[r937]

f.y.i. this --

WHERE 'status' = 'complete'

is always going to be false

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[OsakaWebbie]

I noticed that, too, but decided to give the OP the benefit of the doubt by assuming he was really using backquotes (especially since he said he did get data, just not what he expected). I did, however, replace his "*" with the table name - I don't know if you can use a wildcard for a table name or not, but it looked pretty strange to me.

 

[r937]

no, you cannot use a wildcard for the table name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon