after hours of researching and trying out myself I finally registered on Stackoverflow, which helped me for years by just reading. Thank you very much for this learning opportunity! I now hope to find help with a my MySQL-problem I can't get solved. I'm stuck.
Source: I have a table of orders made by customers.
Output: I want orders grouped by email adress, for every email-adress there should be a sum of total turnover, a count on orders and a row for the first order created and the last order created. Fine until here, could be done by grouping and min() and max() functions. Where I'm stuck: I want the IP-address of the first and last order, the first name of the first and last order and so on (see desired output further down).
What I tried and researched on: Groupwise max, subselect with an order inside the subselect and a group outside, several join variants.
Here is a SQLfiddle with random data and and a sql query I've build. It works sort of: http://sqlfiddle.com/#!9/0272b/6
As an alternative to the SQL in the SQLfiddle i tried this, which works for one email-adress:
SELECT
lastentry.entity_id,
lastentry.customer_email,
firstentry.created_at AS FirstOrder,
lastentry.created_at AS LastOrder,
COUNT(lastentry.entity_id) AS TotalOrders,
SUM(lastentry.grand_total) AS TotalTurnover,
firstentry.entity_id,
firstentry.remote_ip AS FirstIP,
lastentry.remote_ip AS LastIP
FROM
orders lastentry
LEFT OUTER JOIN
(
SELECT
co1.entity_id,
co1.customer_email,
co1.remote_ip,
co1.created_at
FROM
orders AS co1,
(
SELECT
customer_email,
remote_ip,
MIN(created_at) AS maxpop
FROM
orders
GROUP BY
customer_email) AS co2
WHERE
co2.customer_email = co1.customer_email
AND co1.created_at = co2.maxpop ) AS firstentry
ON
(
lastentry.customer_email = firstentry.customer_email )
ORDER BY
lastentry.created_at DESC,
firstentry.created_at ASC
LIMIT 1
I also been trying to do a subselect or join using a subselect in a where-statement, but had no luck:
created_at = (SELECT MAX(t2.created_at)
FROM orders t2
WHERE customer_email= t1.customer_email
)
My actual desired output would look like the following:
| customer_email | FirstOrder | LastOrder | TotalOrders | TotalTurnover | FirstIP | LastIP | FirstName | LastName |
|--------------------------|---------------------|---------------------|-------------|---------------|---------------|----------------|-----------|----------|
| [email protected] | 2014-11-06 16:38:31 | 2014-11-15 11:14:42 | 2 | 116,09 | 103.132.17.9 | 153.241.73.137 | David | David |
| [email protected] | 2014-08-19 06:26:26 | (null) | 1 | 1,1 | 87.217.157.91 | (null) | Frank | (null) |
| [email protected] | 2014-06-01 09:59:10 | (null) | 1 | 95,76 | 117.4.9.206 | (null) | Joshua | (null) |
| [email protected] | 2015-01-30 22:49:56 | (null) | 1 | 57,12 | 220.77.70.87 | (null) | Kevin | (null) |
| [email protected] | 2014-10-27 01:02:46 | (null) | 1 | 90,45 | 122.38.175.17 | (null) | Larry | (null) |
| [email protected] | 2012-11-05 07:56:38 | 2014-06-09 21:57:20 | 3 | 163,58 | 220.75.17.164 | 203.81.207.35 | Steven | Lousie |
Any help is appreciated!
Questions I ask myself:
You could also use subqueries to find the row values of the first and last orders. This assumes that the first and last orders are according to entity_id.
SELECT
customer_email,
COUNT(*) AS total_orders,
SUM(grand_total) AS total_turnover,
(SELECT created_at FROM orders WHERE
entity_id = MIN(t.entity_id)) AS first_created_at,
(SELECT created_at FROM orders WHERE
entity_id = MAX(t.entity_id)) AS last_created_at,
(SELECT remote_ip FROM orders WHERE
entity_id = MIN(t.entity_id)) AS first_remote_ip,
(SELECT remote_ip FROM orders WHERE
entity_id = MAX(t.entity_id)) AS last_remote_ip,
(SELECT customer_firstname FROM orders WHERE
entity_id = MIN(t.entity_id)) AS first_customer_firstname,
(SELECT customer_firstname FROM orders WHERE
entity_id = MAX(t.entity_id)) AS last_customer_firstname
FROM orders AS t
GROUP BY customer_email