hmmm SUMIF question.

[twoplustwo]

I have a data set:

SettlementDate P1 P2 P3
01/04/2008 -55 -55 -55
01/04/2008 7 8 8
01/04/2008 -6 -6 -6
I need to group by settlement date where the data is <0, and in another column where the value is >0.

I can do this is in excel but the set is too large really. Does anybody have a decent way of doing this? I had a look on the webs but some of the stuff hasn't worked

 

[neileg]

Looks like an un-normalised structure to me. Also not sure what you mean by grouping in two differen columns.

Anyway, you can create a calculated field that sums the data and group on that.

 

[twoplustwo]

Hi Neil,

It's not - they represent the changes from one forecast to the next. I've got it anyway - I pulled each column in twice, set a where condition to >0 and summed t'other column.

Seems to work :/

 

[MSAccessRookie]

I don't know what your "other column" is, but a UNION might work here. And what exactly is it that you are going to group? Do you mean you want:

1. The settlement date sums for P1, P2, and P3,
2. The sums of all P1, P2, and P3 for a given settlement date,
3. Something else.

The example only shows one settlement date.

 

[twoplustwo]

Hi MS,

"1. The settlement date sums for P1, P2, and P3"

That one. As per my post - I added P01 etc. twice. In the one column I included a "WHERE P01 > 0" and in the second P01 column I summed the values. Worked pretty well but I guess it wouldn't be too efficient on a huge set but whatever, it was only a one off for some adhoc anaylsis.

 

[MSAccessRookie]

Sounds like a Union query might be appropriate for larger groups as well as your test case

SELECT SettlementDate, (P1 + P2 + P3) AS SUMofPs
From SettlementTable
Where (P1 + P2 + P3) < 0
Group By SettlementDate
UNION ALL
SELECT { Some other Column }, (P1 + P2 + P3) AS SUMofPs
From SettlementTable
Where (P1 + P2 + P3) < 0
Group By { Some other Column }

Note that { Some other Column } needs to be a date to make the Union Sets compatible. You also did not specify what to do if the value of (P1 + P2 + P3) is 0, so you would need to adjust the proper Union member to allow for this case