SQL Query in Access using Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Query in Access using Sum

Author

Topic

PDiTO
Starting Member

1 Post

Posted - 2008-07-09 : 03:33:16

Good morning,

I have a basic database table (BLOTTER) containing several fields. For now it's easiest to just assume they are TD, BO, size, maturity.

TD contains a date.
BO contains either a B or an O.
Size is numeric.
Maturity is number (a year, ie 2008).

I'm trying to run a query that will sum Size and Group results by Maturity for records between specified dates. I have achieved this result with a simple SQL query, however, my problem now is, I want two seperate sum columns. One for BO = B and one for where BO = O. With results again grouped by Year. Of course I can do this in two seperate queries using WHERE BO = "B" and WHERE BO = "O" but I would like to achieve the results in a single query if possible. Please see my attached code...the Bid Total and Offer Total columns are aggregated for all records, and are not grouped by maturity which is what I need.

SELECT BO, MATURITY, Sum(SIZE) AS TOTAL, (SELECT Sum(Size) From BLOTTER WHERE (((TD)>=DateValue('01/07/2008') And (TD)<=DateValue('08/07/2008'))) And BO="B") AS [Bid Total], (SELECT Sum(Size) From BLOTTER WHERE (((TD)>=DateValue('01/07/2008') And (TD)<=DateValue('08/07/2008'))) And BO="O") AS [Offer Total]
FROM BLOTTER
WHERE (((TD)>=DateValue('01/07/2008') And (TD)<=DateValue('08/07/2008')))
GROUP BY BO, MATURITY;

Results are as follows...

BO MATURITY TOTAL Bid Total Offer Total
B 2008 245 16427.9 9892.8
B 2009 2396 16427.9 9892.8
B 2010 6768 16427.9 9892.8
B 2011 3220 16427.9 9892.8
B 2012 968 16427.9 9892.8
B 2014 361 16427.9 9892.8
B 2016 544 16427.9 9892.8
B 2017 877.8 16427.9 9892.8
B 2022 422 16427.9 9892.8
B 2023 46 16427.9 9892.8
B 2026 136.1 16427.9 9892.8
B 2027 444 16427.9 9892.8
O 2009 550 16427.9 9892.8
O 2010 2459 16427.9 9892.8
O 2011 23 16427.9 9892.8
O 2012 1355 16427.9 9892.8
O 2013 1000 16427.9 9892.8
O 2014 2212 16427.9 9892.8
O 2016 1296.5 16427.9 9892.8
O 2017 92.3 16427.9 9892.8
O 2026 675 16427.9 9892.8
O 2027 230 16427.9 9892.8

As you can see the Bid Total and Offer Total are not grouped by Year (or BO but this doesnt matter).

The results i'd like to achieve would be

MATURITY Bid Total Offer Total
2008 245 0
2009 2396 550
2010 6768 2459
2011 3220 23
etc.....

Thank you in advance for your help.

366306
Starting Member

1 Post

Posted - 2008-07-17 : 07:43:20

The below should sort you out ;)

SELECT
Maturity
, SUM(iif(BO='B',Size,0)) as BidTotal
, SUM(iif(BO='O',Size,0)) as OfferTotal
FROM BLOTTER
WHERE TD between #07/01/2008# and #07/17/2008#
GROUP BY Maturity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources