CODEWITHSUNDEEP
mysqlsqlminhaving
risk_ltu
risk_ltu

Reputation: 113

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

Upvotes: 0

Views: 47

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT 
client as 'Clients first order in 2017', 
SUM(income)  as 'Sum of income in 2018'
FROM orders 
WHERE client IN
(
    SELECT client 
    FROM orders 
    GROUP BY client
    HAVING MIN(YEAR(order_date)) = 2017
)
AND YEAR(order_date) = 2018
GROUP BY client

Upvotes: 0

Related Questions