Question 202460 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL for Smarties type question: Select records from table, with some values grouped

I have a database table with content like this:

Id Label GroupId DueDate Amount
1 (null) A 1/1/2023 100.00
2 (null) A 1/2/2023 101.00
3 L1 A 1/3/2023 102.00
4 L1 A 1/4/2023 103.00
5 (null) B 1/3/2023 104.00
6 L1 C 1/3/2023 105.00
7 (null) A 1/4/2023 106.00
8 (null) A 1/5/2023 107.00
9 L2 A 1/5/2023 108.00
10 L2 A 1/6/2023 109.00

What I’d like to accomplish with a select statement is reporting the records with the same values for ‘label’ and ‘group id’ in a single line item providing a sum() for the amount and a min() for the date. Records that have a null value in the label column should not be grouped.

My ideal result set would look like this:

Label GroupId DueDate Amount
(null) A 1/1/2023 100.00
(null) A 1/2/2023 101.00
L1 A 1/3/2023 205.00
(null) B 1/3/2023 104.00
L1 C 1/3/2023 105.00
(null) A 1/4/2023 106.00
(null) A 1/5/2023 107.00
L2 A 1/5/2023 217.00

If it makes any difference, my RDBMS is Postgres, and the data table in question has about 500 million rows of data.

Is this possible as a single select statement using a CASE clause? Or is it necessary to perform this as multiple selects with a UNION ALL in a manner like this:

select label, groupid, min(duedate), sum(amount) from my_table where label is not null
group by label, groupid
union all
select label, groupid, duedate, amount from my_table where label is null

Any advice would be appreciated.

Tags:

2

Answers


  1. 0

    Your UNION ALL query is a good solution. If you don’t want this for some reason, you can use your table’s ID to get a separate "group" for each label-null row.

    select label, groupid, min(duedate), sum(amount)
from my_table
group by case when label is null then id end, label, groupid;
    Login or Signup to reply.
  2. 0

    Another option might be to use windowed aggregates:

    select label, groupid, duedate, amount
from (
  select Id, label, groupid, 
    First_Value(duedate) over(partition by case when label is null then Id end, groupid order by duedate) DueDate,
    Sum(amount) over(partition by case when label is null then Id end, groupid) Amount,
    Dense_Rank() over(partition by case when label is null then Id end, groupid order by duedate) rn
  from t
)t
where rn = 1
order by id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search