SQL Question

[Guest]

I'm trying to write the following:

SELECT Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest, Sum(Funding.FundingUSD) AS SumOfFundingUSD
FROM (Funding INNER JOIN Inventory ON Funding.InstrumentID =
Inventory.[Instrument Id]) INNER JOIN PL ON Funding.InstrumentID =
PL.InstrumentId
GROUP BY Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest;

The output works but the Sum(funding) part still does not give me the total
sum per instrumentId. Can anyone assist?

 

[John Vinson]

I'm trying to write the following:

SELECT Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest, Sum(Funding.FundingUSD) AS SumOfFundingUSD
FROM (Funding INNER JOIN Inventory ON Funding.InstrumentID =
Inventory.[Instrument Id]) INNER JOIN PL ON Funding.InstrumentID =
PL.InstrumentId
GROUP BY Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest;

The output works but the Sum(funding) part still does not give me the total
sum per instrumentId. Can anyone assist?

Without knowing more about the structure of your table and the data,
all I can suggest is that you're not GROUPING by InstrumentID; you're
grouping by that and seven other fields. What do you *want* to group
by? If there are multiple MaturityDates for each instrument, do you
want to sum the FundingUSD values over all those dates? Which date (if
any) do you want displayed? What about Dept - does one InstrumentID
have multiple Dept values or only one?

It sounds like you may just be including some fields that you don't
need in the Grouping clause.

John W. Vinson[MVP]

 

[Guest]

The simple error was not summing up the CRInterest field. It works when you
get that done.

Thanks all.

I'm trying to write the following:

SELECT Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest, Sum(Funding.FundingUSD) AS SumOfFundingUSD
FROM (Funding INNER JOIN Inventory ON Funding.InstrumentID =
Inventory.[Instrument Id]) INNER JOIN PL ON Funding.InstrumentID =
PL.InstrumentId
GROUP BY Funding.COBDate, Funding.Dept, Funding.InstrumentID,
Funding.Description, PL.LBPosition, Inventory.MaturityDate, PL.[Diff PandL
(USD)], Funding.CRInterest;

The output works but the Sum(funding) part still does not give me the total
sum per instrumentId. Can anyone assist?

Without knowing more about the structure of your table and the data,
all I can suggest is that you're not GROUPING by InstrumentID; you're
grouping by that and seven other fields. What do you *want* to group
by? If there are multiple MaturityDates for each instrument, do you
want to sum the FundingUSD values over all those dates? Which date (if
any) do you want displayed? What about Dept - does one InstrumentID
have multiple Dept values or only one?

It sounds like you may just be including some fields that you don't
need in the Grouping clause.

John W. Vinson[MVP]