Problems using NOT IN with sum function in MS Query

[jack10063]

Hi All,

I am trying to combine sum function and NOT IN logical operator in MS Query SQL but it's not working and I am getting an error "Invalid field definition '000' in definition of index or relationship".

Below is my code:

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>

SELECT A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, SUM(A.TOTAL) AS [TOTAL], B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>
<o:p></o:p>
FROM BondIsin.txt A, BondIsinAcu.txt B<o:p></o:p>
<o:p></o:p>
WHERE A.`BOND ISIN CODE` = B.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('XSDE0002449139')<o:p></o:p>
<o:p></o:p>
GROUP BY A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>

The problem is the aggregate function Sum. Below is a code without summing and GROUP BY statement that doesn't give an error:

SELECT A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, A.TOTAL, B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>
<o:p></o:p>
FROM BondIsin.txt A, BondIsinAcu.txt B<o:p></o:p>
<o:p></o:p>
WHERE A.`BOND ISIN CODE` = B.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('XSDE0002449139')<o:p></o:p>

So the question is: Which statement can I combine with aggregate functions to filter the data..? I tried to use CASE statement but couldn't get it to work either.. Thanks for any help!

-Jack

 

[boblarson]

The problem is you can't use your ALIAS in the GROUP BY. So you need the original:

SELECT A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, SUM(A.TOTAL) AS [TOTAL], B.PV, B.`BOND ISIN CODE`, B.TOTAL
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
FROM BondIsin.txt A, BondIsinAcu.txt B
<o:p></o:p>
WHERE A.`BOND ISIN CODE` = B.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('XSDE0002449139')
<o:p></o:p>
GROUP BY A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, SUM(A.TOTAL), B.`BOND ISIN CODE`, B.TOTAL

 

[jack10063]

Thanks for your answer. I tried to use SUM(A.TOTAL) in GROUP BY statement but I am getting an error "Cannot have aggregate function in GROUP BY clause (SUM(A.TOTAL))"...

Thanks for any ideas!

-Jack

 

[xenou]

You shouldn't need Sum() in the group by anyway - its only the other fields that need to be in that clause (the ones without Sum, Count, Avg, Max, Min, etc.)

 

[jack10063]

xenou,

Thanks for your answer. I understood that I shouldn't use SUM() in GROUP BY.. Do you have any ideas how to solve my problem..?

Thanks!

-Jack

 

[xenou]

Hmmm, Sorry getting confused by all the examples and counter examples.

Have you tried Bob's suggestion?

 

[jack10063]

I tried Bob's solution but I couldn't get it to work.. The problem was that I am joining two tables with LEFT JOIN and in the "left" table I have the same bond multiple times and in the "right table" I have the bond only once with values summed. Thus, I have to sum the deals with same code in the "left" table to compare them with the "right" one.

It is working okay if I don't wan't to exclude any deals.. But I would like to use NOT IN to exclude a few bonds.. but I am not able to get it working because I am using SUM() in the "left" table (no problems if I wouldn't have to sum the deals)..

For some reason I am not able to find a solution.. Sorry if I am being confusing..

-Jack

 

[xenou]

Can't stop right now but a nested query structure might work.

Or, you can also query for the values excluding the bonds you don't want in a separate query, and then join to that query (being one without those bonds).

 

[boblarson]

Use a subquery to sum the side which needs summing and then use that in place of the table in the main query.

 

[jack10063]

Thanks for the ideas. I am trying to use a subquery to sum the values in the left table. However I am still struggling a bit:

With a code below I am able to use NOT IN:

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]SELECT A.`BOND ISIN CODE`, A.TOTAL, D.TOTAL[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]FROM BondIsin.txt A, (SELECT  B.`BOND ISIN CODE`, SUM(B.TOTAL) AS [TOTAL] FROM  BondIsin.txt B GROUP BY  B.`BOND ISIN CODE`) C, BondIsinAcu.txt D WHERE A.`BOND ISIN CODE`=C.`BOND ISIN CODE`   AND A.`BOND ISIN CODE`=D.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('BE6000075430')[/COLOR][/FONT]
[/COLOR][/FONT]

But it doesn't sum the values with the same code. Even though I defined a subquery

(SELECT  B.`BOND ISIN CODE`, SUM(B.TOTAL) AS [TOTAL] FROM  BondIsin.txt B GROUP BY  B.`BOND ISIN CODE`) C

To sum the values in TOTAL

And then using A.`BOND ISIN CODE`=C.`BOND ISIN CODE` to match the deals with the main table, A, and subtable C...

any ideas what I have done wrong in my subquery.. I guess I have to keep on trying... . Thanks for help!

-Jack