1

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

0

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;
  • Thanks. Is there any way to perform an operation with the SUM? For example, having a columnpay_year + SUM(pay_unit). How should I proceed with the GROUP BY statement? Is there any way to put column aliases in the GROUP BY? – phollox Aug 29 '18 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.