J H J H - 1 year ago 69
MySQL Question

I can't figure out how to calculate the value of each customer for their first year in MySQL

I am trying to calculate how much each customer spends in their first year with us but I'm struggling with how to format the query. So far I have:

select customerId, sum(total)
from cms2_orders
where customerId = 254063
(dtc between
(select min(dtc)
from cms2_orders
group by customerId
(select (date_add(min(dtc), interval 1 year))
from cms2_orders))
group by customerId

I know the problem is with the 'group by' on line 8 but if that is not present, it is interpreted as the first order ever placed by anyone. Is there a different way of doing this?

Answer Source

One cleaner way of doing this is to use a subquery to calculate the start date for each customer, and then join this to your main query to remove records from the total which did not occur in the first year.

SELECT t1.customerid,
FROM cms2_orders t1
    SELECT customerid,
           MIN(dtc) AS dtc_start
    FROM cms2_orders
    GROUP BY customerid
) t2
    ON t1.customerid = t2.customerid
WHERE t1.dtc BETWEEN t2.dtc_start AND DATEADD(t2.dtc_start, INTERVAL 1 YEAR)
GROUP BY t1.customerid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download