SQL Query for records

I want to write a SQL query to get problem solution given below – I have a table something like this:

CREATE TABLE order 
(
    id INT,
    name VARCHAR(255),
    income INT,
    order_date DATE
)

Pardon if something wrong with query but you get the idea.

Now I want sql query to give result like total income of users for year 2017 whose first order was created in 2016.

Answer

Your order table seems an odd design but given

drop table if exists t;
create table t
(
    id INT,
    name VARCHAR(255),
    income INT,
    order_date DATE
);

insert into t values
(1,'aaa',10,'2016-01-01'),(1,'aaa',10,'2017-01-01'),(1,'aaa',10,'2017-01-01'),
(2,'bbb',10,'2015-01-01'),(2,'bbb',10,'2016-01-01'),(2,'bbb',10,'2017-01-01'),
(3,'ccc',10,'2017-01-01'),(3,'ccc',10,'2017-01-01'),(3,'ccc',10,'2018-01-01')
;

a simple join might be all you need

select t.name,sum(t.income)
from t
join (select name,min(year(order_date)) minyr from t  group by name) s 
        on t.name = s.name
where year(order_date) = 2017 and minyr = 2016
group by name;

+------+---------------+
| name | sum(t.income) |
+------+---------------+
| aaa  |            20 |
+------+---------------+
1 row in set (0.00 sec)