How to use MIN() by date in a clause that uses SUM() for another date?

I need to find all the clients that have their first order in 2017 and sum up their incomes in 2018. I have issue trying to do that in SQL clause.

In my table I have 3 clients, 2 of them first ordered in 2017-05-23, and 1 in 2016-06-23. So I want to filter out that one client that ordered in 2016. In a table I have fields: client, income, order_date.

This is what I have in MySQL:

select client as 'Clients first order in 2017',
       SUM(income) as 'Sum of income in 2018'  
from orders
where YEAR(order_date) = 2018
  and (select client, order_date
       from orders
       group by client
       having min(year(order_date)) = 2017)
order by client

I am expecting to get all the clients that first ordered in 2017 and sum up their incomes for 2018

Answer

Use conditional aggregation:

Select client as 'Clients first order in 2017', 
        SUM(CASE WHEN YEAR(order_date) = 2018 
                 THEN income 
                 ELSE 0 
            END) as 'Sum income in 2018'  
FROM orders
GROUP BY client
HAVING MIN (YEAR(order_date)) = 2017
order by client

Advertisement

Answer