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

Postgresql – SQL – Select rows that share same master account but different sub accounts with different values

I am trying to formulate a query in PostgresSQL, my table looks like this:

Master_Account Sub_Account Cost
123 777 15
123 888 10
123 999 20
456 111 10
456 222 -15
789 789 10

I am trying to select rows where the master account and sub accounts are different values (there are cases where they are the same, like the last row), and where cost is both positive and negative under the master account. So my desired output would be only these rows:

Master_Account Sub_Account Cost
456 111 10
456 222 -15

I am essentially trying to select rows where Master Account and Sub Account are different, and where cost contains both positive and negative values, not both positive or both negative.

I was thinking something like:
select * from table where master_account != sub_account and where exists (cost > 0 and cost < 0)

Tags:

2

Answers


  1. 0

    There are more succinct ways to write this, but this is a way that doesn’t use window functions.

    Rationale – Simple where clause to get the records with different master and sub account numbers. Then an exists to find the ones which have at least one different sign for the master number.

    Edited for the additional stipulation in the comment. There’s a couple ways to interpret that new request. If it’d satisfy that it must be a different sub_account with a different sign, then simply

         (
       select 1
         from accounts_with_different_sub t2
        where t1.master = t2.master
          and sign(t1.cost) <> sign(t2.cost)
          and t1.sub_account <> t2.sub_account
     )

    works. If for the same sub_account it’d satisfy if there was a different sign, and a different sub_account with an arbitrary sign you’d do it like this:

    CREATE TABLE AccountData (
    Master INT NOT NULL,
    Sub_Account INT NOT NULL,
    Cost INT NOT NULL
);

INSERT INTO AccountData (Master, Sub_Account, Cost) VALUES
    (123, 777, 15),
    (123, 888, 10),
    (123, 999, 20),
    (456, 111, 10),
    (456, 222, -15),
    (789, 789, 10);

with accounts_with_different_sub as 
  (
    select * 
      from AccountData
     where master <> sub_account
  )
select * 
  from accounts_with_different_sub t1
 where exists
         (
           select 1
             from accounts_with_different_sub t2
            where t1.master = t2.master
              and sign(t1.cost) <> sign(t2.cost)
         )
   and exists
         (
           select 1
             from accounts_with_different_sub t3
            where t1.master = t3.master
              and t1.sub_account <> t3.sub_account
         )

    Edit:

    And just to show a common trick using window functions. min() over () <> max() over () is handy when you are looking for distinct values over a grouping.

    with accounts_with_different_sub as 
  (
    select *,
           case when max(sign(cost)) over ( partition by master ) <> 
                     min(sign(cost)) over ( partition by master )
                 and max(sub_account) over ( partition by master ) <> 
                     min(sub_account) over ( partition by master )
                then 1
                else 0 
            end as has_multiple_signs_and_different_subs
      from AccountData
     where master <> sub_account
  )
select * 
  from accounts_with_different_sub t1
 where has_multiple_signs_and_different_subs= 1
    Login or Signup to reply.
  2. 0 
    WITH CTE(Master_Account,Sub_Account,Cost)AS 
(
SELECT 123,     777,    15 UNION ALL 
SELECT 123,     888,    10 UNION ALL 
SELECT 123,     999,    20 UNION ALL 
SELECT 456,     111,    10 UNION ALL 
SELECT 456,     222,    -15 UNION ALL 
SELECT 789,     789,    10
)
SELECT C.*
FROM CTE AS C
WHERE C.Master_Account<>C.Sub_Account
AND EXISTS 
(
 SELECT 1 FROM CTE AS C_POS 
    WHERE C.Master_Account=C_POS.Master_Account
  AND C_POS.Cost>0.00
)
AND EXISTS
(
  SELECT 1 FROM CTE AS C_NEG
   WHERE C.Master_Account=C_NEG.Master_Account
  AND C_NEG.Cost<0.00
)

    Could you please try

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search