SQL Query for records

Question

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.

@RenéNyffenegger I am using mysql. For trying i was not able to pin point any query as unable to think of any logical query for this result. — kshitij, Jul 18 '19 at 05:02
Note that order is a reserved word and in MySQL, making it a singularly poor choice as a table/column identifier — Strawberry, Jul 18 '19 at 07:31

score 1 · Accepted Answer · answered Jul 18 '19 at 06:57

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)

SQL Query for records

1 Answers1