0

I have the following queries

select
o.increment_id, 
o.customer_id, 
round(o.base_grand_total * o.base_to_global_rate,2) as revGBP,
DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder',
from 
hephaestus.sales_flat_order as o
Where  
o.status in ('complete', 'processing')
and
o.customer_email is not null
;

This extracts every order placed at my company

|increment id | Customer ID | revGBP | MonthofOrder | 
-----------------------------------------------------
| 123456     | 1            | 100    | 1-8-2014     |
-----------------------------------------------------
|123         | 1            | 200    |1-9-2014      |
----------------------------------------------------- 
|1234        | 2            | 250    |1-6-2014      |
-----------------------------------------------------
|123213      | 1            | 50     |1-7-2014      |
-----------------------------------------------------
|1235657     | 3            | 200    |1-9-2014      |
-----------------------------------------------------

My second query

select  
o.customer_ID as 'Email',
date_format(max(o.created_at), '01-%m-%Y') as 'LastOrderMonth',
sum(round((o.base_grand_total * o.base_to_global_rate),2)) as 'Rev GBP',
date_format(min(o.created_at), '01-%m-%Y')  as 'monthCustCreated',
count(o.increment_id) as 'CountOfOrders'
from    
hephaestus.sales_flat_order as o
where
status in ('complete', 'processing')
and
o.customer_ID is not null
group by 
o.customer_ID
;

This gives a top level view of customers spend so the output would look like

|Customer id |LastOrderMonth| revGBP |MonthCustCreated|CountOfOrders|
---------------------------------------------------------------------
|1           |1-9-2014      |350     |1-7-2014        | 3           |
---------------------------------------------------------------------
|2           |1-6-2014      |250     |1-6-2014        | 1           |
---------------------------------------------------------------------
|3           |1-9-2014      |200     |1-9-2014        | 1           |
---------------------------------------------------------------------

The data is coming from the same table, I want to combine them, so I get the complete order table and then the customer summary attached to each row, I know this will bring in duplicate data when the customer ID matches but this is expected.

To be honest I am not a MySQL bod, so any help would be greatly appriciated.

Thanking anyone who can help in advance.

1
  • 1
    Do a LEFT JOIN on customer_id
    – Mihai
    Commented Sep 26, 2014 at 10:45

2 Answers 2

0

Here is what you asked for, but I think you would want to further aggregate this result set by GROUP BY customer_id to roll up the joined table. If that is the case, then simply add GROUP BY o.customer_id.

SELECT
  o.increment_id, 
  o.customer_id, 
  b.LastOrderMonth,
  ROUND(o.base_grand_total * o.base_to_global_rate,2) as revGBP,
  DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder',
  b.monthCustCreated,
  b.CountOfOrders
FROM 
  sales_flat_order as o
LEFT JOIN 
(
    SELECT  
      customer_id,
      date_format(MAX(created_at), '01-%m-%Y') as 'LastOrderMonth',
      sum(round((base_grand_total * base_to_global_rate),2)) as 'Rev GBP',
      date_format(MIN(created_at), '01-%m-%Y')  as 'monthCustCreated',
      COUNT(increment_id) as 'CountOfOrders'
    FROM    
      sales_flat_order
    WHERE
      status in ('complete', 'processing')
    AND
      customer_ID IS NOT NULL
    GROUP BY 
      customer_ID
) b ON o.customer_id = b.customer_id
WHERE  
  o.status in ('complete', 'processing')
AND
  o.customer_email IS NOT NULL;
0

My naive sugestion is

select
    *
from (<your first query>) as one
inner join (<your second query>) as two
on one.customer_id = two.customer_id;

There are a lot of performance improvement that could be made over this, however, with more thorough refactoring.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.