Select just one row for equal parameters and all rows if they differ

Question

I have a database table:

| id | account | extra_account | state |special_value

I need to select extra_accounts, which are connected with a list of accounts.

SELECT * FROM table
WHERE table.account in (111, 222, 333) and table.state = 'WORKS';

|id  | account         | extra_account     | state         |special_value
—-------------------------------------------------------------------------------
|100 |111              |111-1              |WORKS          |1
|200 |111              |111-2              |WORKS          |1
|300 |222              |222-1              |WORKS          |2
|400 |333              |333-1              |WORKS          |3
|500 |333              |333-2              |WORKS          |4

I have to concatenate extra_accounts in one String separated by a comma.

If an account has two or more extra_accounts and their special_value are the same I have to take one extra_accounts, no matter which. So for id=100 and id=200 I have to take just one extra_account - 111-1 or 111-2, as their special_values are equal.

If an account has two or more extra_accounts and their special_value are different I have to take all of them. So for id=400 and id=500, I need both - 333-1 and 333-2, as their special_values are 3 and 4.

The final result should be:

|string_agg
|text
—----
|111-1, 222-1, 333-1, 333-2

I know that I can concat values by using:

SELECT  string_agg(table.extra_account, ', ')
FROM table WHERE table.account in (111, 222, 333) and table.state = 'WORKS';

But I didn’t find a way to select all rows if special_values are different and only one row if special_values are equal.

This one will help https://stackoverflow.com/questions/24785084/issue-with-string-agg-with-distinct-in-postgres — ekochergin, Dec 21 '21 at 12:20

Erwin Brandstetter · Accepted Answer · 2021-12-21T14:35:09.893

That's what you want per account:

SELECT account
     , CASE WHEN count(DISTINCT special_value) = 1
            THEN min(extra_account)
            ELSE string_agg(extra_account, ', ')
            END AS special_values
FROM   tbl
WHERE  account IN (111, 222, 333)
AND    state = 'WORKS'
GROUP  BY 1;

Wrap the above in a subquery and aggregate:

SELECT string_agg(special_values, ', ')
FROM (
   SELECT account
        , CASE WHEN count(DISTINCT special_value) = 1
               THEN min(extra_account)
               ELSE string_agg(extra_account, ', ')
               END AS special_values
   FROM   tbl
   WHERE  account IN (111, 222, 333)
   AND    state = 'WORKS'
   GROUP  BY 1
   ) sub;

Produces your desired result exactly.

If you really want a single extra_account per distinct special_value (unlike expressed):

SELECT string_agg(extra_account, ', ')
FROM  (SELECT DISTINCT ON (account, special_value) extra_account FROM tbl) sub;

db<>fiddle here - with an added row to show the difference

About DISTINCT ON:

Select first row in each GROUP BY group?

(And why it's typically faster.)

LukStorms · Answer 2 · 2021-12-21T12:55:00.540

1

If you calculate a ROW_NUMBER then you can take 1 based on account & special_value.

SELECT 
 STRING_AGG(DISTINCT extra_account, ', ' ORDER BY extra_account) AS extra_accounts
FROM (
  SELECT account, extra_account, special_value
  , ROW_NUMBER() OVER (PARTITION BY account, special_value ORDER BY id) AS rownum
  FROM "table" t
  WHERE account IN (111, 222, 333) 
    AND state = 'WORKS' 
) q
WHERE rownum = 1;

extra_accounts
111-1, 222-1, 333-1, 333-2

Demo on db<>fiddle here

edited Dec 21 '21 at 12:55

answered Dec 21 '21 at 12:43

LukStorms

28,916
5
31
45

Clever. But it fails for accounts that have *some*, but not all duplicate special values. See: *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=d068b80d2c32e501900f6dc8e979e16d)* (Unless the OP didn't specify properly ...) – Erwin Brandstetter Dec 21 '21 at 12:51
@ErwinBrandstetter Well, we'll see what nikiforov comments about it. But that extra special_value 4 shouldn't be taken, as I understand the question anyway. – LukStorms Dec 21 '21 at 12:59

Anton Grig · Answer 3 · 2021-12-21T13:37:26.723

1

I would do this via the aggregate function min on the columns account and special_value.

With CTE As (
Select account, special_value, Min(extra_account) As v
From Tbl
Where account In (111, 222, 333) And state = 'WORKS'
Group by account, special_value
)
Select string_agg(v, ',' Order by v)
From CTE

Data Output:

string_agg
111-1,222-1,333-1,333-2

edited Dec 21 '21 at 13:37

answered Dec 21 '21 at 13:31

Anton Grig

1,640
7
11

Select just one row for equal parameters and all rows if they differ

3 Answers3