Query to partially summarize data in a table using SQL

Question

I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I tried).

My data is on LibreOffice, which runs HSQLDB v1.8 (very old engine version). For the moment, I have to stick to it. Using a more up-to-date SQL engine is not possible.

I have a payment table with the following structure

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2017     1
0          2018     2
1          2015     1
1          2018     2

I need to sumarize this table into something like

member_id  pay_year pay_unit_year
0          2016     1
0          2017     4
0          2018     2
1          2017     1
1          2018     2

The pay_unit_year is the number of years of membership purchased. So eventually I need to operate with the years and units, and getting a single line per member

member_id  expire_year
0          2023
1          2020

I'm using

SELECT DISTINCT member_id,
pay_year,
pay_unit
FROM payments

and I'm getting

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2018     2
1          2017     1
1          2018     2

Is there any way to perform an operation on a subset of pay_unit that has the other columns the same information?

I'm aware that SQL has many available date operations, but HSQLDB v1.8 has not them implemented.

Thanks for any help, and for your patience

Answer 1

It's indeed a very basic concept: aggregation. You need the GROUP BY clause:

SELECT member_id,
pay_year,
SUM(pay_unit) AS sum_pay_unit
FROM payments
GROUP BY member_id, pay_year;