Help with aggregate function

Question

Help with aggregate function

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ger_serrano SSC Veteran Points: 272 More actions · Answer 1

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: 272 More actions · Answer 2

ger_serrano

SSC Veteran

Points: 272

December 20, 2017 at 10:39 am

#1972955

my apologies for the name confusion J. Drew.

drew.allen SSC Guru Points: 76944 More actions · Answer 3

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: 272 More actions · Answer 4

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: 1003871 More actions · Answer 5

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)