Help with aggregate function

• ger_serrano

  SSC Veteran

  Points: 282

  More actions

  December 20, 2017 at 10:28 am

  #1972951

  I will check that part Jeff. I was checking all the possible scenarios and I think the query does not work for the following set of data:
  create table VALUATION ( col1 VARCHAR(10), col2 VARCHAR(10), MTM numeric(10));
  INSERT INTO VALUATION VALUES
  ('A','B',30),
  ('A','B',-80),
  ('a','B',43),
  ('A','B',85),
  ('B','A',-150),
  ('B','A',45),
  ('b','a',95),
  ('B','A',-5),
  ('A','C',200),
  ('A','D',100),
  ('B','F',200),
  ('d','K',400),
  ('K','D',-20),
  ('C','D',-50),
  ('D','C',-60),
  ('X','Y',-25),
  ('P','Q',-20),
  ('N','M',-15),
  ('Y','X',-25);
  Row ('P','Q',-20) should be reported as Q P 20. There are no more trades between P and Q and the sign switch occurs only when col1 >= col2.

• ger_serrano

  SSC Veteran

  Points: 282

  More actions

  December 20, 2017 at 10:39 am

  #1972955

  my apologies for the name confusion J. Drew.

• drew.allen

  SSC Guru

  Points: 77012

  More actions

  December 20, 2017 at 2:31 pm

  #1973003

  ger_serrano - Wednesday, December 20, 2017 10:28 AM

  I will check that part Jeff. I was checking all the possible scenarios and I think the query does not work for the following set of data:
  create table VALUATION ( col1 VARCHAR(10), col2 VARCHAR(10), MTM numeric(10));
  INSERT INTO VALUATION VALUES
  ('A','B',30),
  ('A','B',-80),
  ('a','B',43),
  ('A','B',85),
  ('B','A',-150),
  ('B','A',45),
  ('b','a',95),
  ('B','A',-5),
  ('A','C',200),
  ('A','D',100),
  ('B','F',200),
  ('d','K',400),
  ('K','D',-20),
  ('C','D',-50),
  ('D','C',-60),
  ('X','Y',-25),
  ('P','Q',-20),
  ('N','M',-15),
  ('Y','X',-25);
  Row ('P','Q',-20) should be reported as Q P 20. There are no more trades between P and Q and the sign switch occurs only when col1 >= col2.

  
  You're moving the goalposts.  P|Q|-20 is equivalent to Q|P|20.  The code works. This is merely a presentation issue.  See if you can add to the code to get it into the desired presentation.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• ger_serrano

  SSC Veteran

  Points: 282

  More actions

  December 21, 2017 at 7:34 am

  #1973081

  Drew thank you very much for your help. Really apreciated. I just had to test all the possible data scenarios. My real table has more than 160 million records. However as you said I already modified the code to get the desired result.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  December 21, 2017 at 7:53 am

  #1973086

  ger_serrano - Wednesday, December 20, 2017 7:34 AM

  Awesome Jeff!!.  Thanks a lot. I think the following query is also rdbms agnostic:
  with newtab (col1,col2, mtm)
  as
  (SELECT upper(col1), upper(col2), mtm FROM sampledata
    WHERE upper(col1) < upper(col2)
      
   UNION ALL

    SELECT upper(col2), upper(col1), -mtm FROM sampledata
    WHERE upper(col1) >= upper(col2)
      )
  select col1, col2, sum(mtm) as mtm from newtab
  group by col1, col2;

  To be sure, that was actually Drew.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)