SQL SUMs promblems (1 Viewer)

[DonkeyKong]

My SUMs are getting screwed up...

If I do this I get the correct value:

SELECT HOLDERS.CUSIP, Sum(HOLDERS.ORIGINAL_FACE) AS [ORIGINAL FACE]
FROM HOLDERS INNER JOIN TEMP_HOLD ON TEMP_HOLD.CUSIP = HOLDERS.CUSIP WHERE HOLDERS.CUSIP IS NOT NULL
GROUP BY HOLDERS.CUSIP
ORDER BY HOLDERS.CUSIP

But if I do this, my value gets thrown waaay off:

SELECT HOLDERS.CUSIP, Sum(HOLDERS.ORIGINAL_FACE) AS [ORIGINAL FACE]
FROM (HOLDERS INNER JOIN TEMP_HOLD ON TEMP_HOLD.CUSIP = HOLDERS.CUSIP) INNER JOIN [300_HOLDERS] ON TEMP_HOLD.CUSIP = [300_HOLDERS].CUSIP
WHERE HOLDERS.CUSIP IS NOT NULL
GROUP BY HOLDERS.CUSIP
ORDER BY HOLDERS.CUSIP

Why is this happening? I know it is something with my join. Is there a way I can fix this without making another seperate query? Thanks.

 

[RainLover]

SELECT HOLDERS.CUSIP, Sum(HOLDERS.ORIGINAL_FACE) AS [ORIGINAL FACE]
FROM (HOLDERS INNER JOIN TEMP_HOLD ON TEMP_HOLD.CUSIP = HOLDERS.CUSIP) INNER JOIN [300_HOLDERS] ON TEMP_HOLD.CUSIP = [300_HOLDERS].CUSIP
GROUP BY HOLDERS.CUSIP
WHERE HOLDERS.CUSIP IS NOT NULL
ORDER BY HOLDERS.CUSIP

I think it is Group By, Where then Order By.

Not Tested

 

[DonkeyKong]

Hmm... didn't seem to work. I think you can only use the GROUP BY clause if you have a WHERE clause

**edit** you can use GROUP BY without WHERE, but WHERE must come before GROUP BY

 

[plog]

My guess is that [300_HOLDERS] has mutliple records for the same CUSIP values. If that's correct, you will need to create a sub query on [300_HOLDERS] to get unique CUSIP values.

While your at it I would check TMP_HOLD for multiple records for the same CUSIP value. These queries will tell you if my guess is right:

SELECT CUSIP, COUNT(CUSIP) AS Tot FROM [300_HOLDERS] GROUP BY CUSIP ORDER BY COUNT(CUSIP) DESC;


SELECT CUSIP, COUNT(CUSIP) AS Tot FROM [TMP_HOLD] GROUP BY CUSIP ORDER BY COUNT(CUSIP) DESC;

If the top Tot value in either query is over 1, then that's the issue.

 

[RainLover]

Good advice from Plog.

You could try creating the SQL by first creating a query. When you get the query working you can simply copy the SQL for use in other places.

 

[DonkeyKong]

Plog you are correct. There are multiple entries on the 300_HOLDERS table and it is taking my HOLDERS value * (number of 300_HOLDERS entries). Any ideas on a sub query that can help me with this? I would really like to get all of this on one line, in one query. Think that's possible?

 

[DonkeyKong]

Got it. Thanks guys!

SELECT HOLDERS.CUSIP, SUM(HOLDERS.ORIGINAL_FACE) AS [400 ORIGINAL FACE], 
(SELECT SUM([300_HOLDERS].ORIGINAL_FACE) AS [300 ORIGINAL]
FROM [300_HOLDERS] 
WHERE [300_HOLDERS].CUSIP = TEMP_HOLD.CUSIP) AS [300 ORIGINAL FACE]
FROM HOLDERS INNER JOIN TEMP_HOLD ON TEMP_HOLD.CUSIP = HOLDERS.CUSIP
WHERE HOLDERS.CUSIP IS NOT NULL
GROUP BY HOLDERS.CUSIP, TEMP_HOLD.CUSIP
ORDER BY HOLDERS.CUSIP
UNION ALL SELECT TEMP_HOLD.CUSIP, NULL AS [400 ORIGINAL FACE], NULL AS [300 ORIGINAL FACE]
FROM TEMP_HOLD LEFT JOIN HOLDERS ON TEMP_HOLD.CUSIP=HOLDERS.CUSIP
WHERE (HOLDERS.CUSIP) Is Null
ORDER BY CUSIP;

 

[plog]

SELECT DISTINCT CUSIP FROM [300_HOLDERS];

Use that query instead of [300_HOLDERS] in the query you have currently.

 

[RainLover]

plog is more than just a pretty face.

 

[DonkeyKong]

In the original query or the one with the sub query? The full length one I posted up gets the correct results. I'm also not sure that I can use distinct like that. I think it has to come at the beginning of the select statement and then applies to all fields listed.

 

[plog]

Ha ha, thanks Rain.

You need to use my query with Distinct in it in your query that didn't work:

SELECT HOLDERS.CUSIP, Sum(HOLDERS.ORIGINAL_FACE) AS [ORIGINAL FACE]
FROM (HOLDERS INNER JOIN TEMP_HOLD ON TEMP_HOLD.CUSIP = HOLDERS.CUSIP)  INNER JOIN [300_HOLDERS] ON TEMP_HOLD.CUSIP = [300_HOLDERS].CUSIP
WHERE HOLDERS.CUSIP IS NOT NULL
GROUP BY HOLDERS.CUSIP
ORDER BY HOLDERS.CUSIP

First paste the SQL with DISTINCT into a query and save it, naming it like MainQuery_sub. Then in design view of your query bring it in, link it just like [300_HOLDERS] is linked now, delete [300_HOLDERS] and run your query.

 

[DonkeyKong]

Alright cool. I want it all in one query though cause I have around 20 queries already and if I didn't make them relatively complex I would probably have more like 50. At 20 it's confusing, at 50 it's a headache.

 

[plog]

We've only been talking about 1 query (2 if you count the sub), so I have no idea the other 19 you are referencing.

 

[DonkeyKong]

Haha. I'm talking about the sub query. If I made a separate subquery everytime I would have a lot of queries and it would be difficult to keep them in order. That is why I am using my solution. I placed the subquery in the select statement and thus have all SQL in '1' query. Don't think I'm not grateful for your help, I just only want to run 1 query to obtain the desired results. My solution does this.

 

[plog]

I just reviewed your UNION query and saw the 400 and 300 prefixed data sources. My guess is you have some inefficiencies here. If they are tables, your data probably isn't structured correctly, if queries then you probably need to revaluate them.

Which are they (tables or queries) and do the similarly named data sources have the same fields?

 

[DonkeyKong]

They are tables. They have the same column layout, only the values differ. I do have a small problem with the query but I am almost done with it. I will post up the code for your review.

Problem:
TYPICALLY 300 values will only show up if there are 400 values. However this is not always the case. The problem I have is that because the 400 table has no value for CUSIP and it is what is being joined, the 300 table is not checked for value. Once again though I am fixing this problem.

 

[plog]

They have the same column layout, only the values differ...Once again though I am fixing this problem

Actually you're fixing the symptom, the problem is your table structure. If these tables have the same fields, they need to have their data all together in one table. You don't store information relevent to the data in a field or table name, you store that information at the record level. You need one table with the same layout as you currently have, but with one more field--that field would hold if the data is 300 or 400 or whatever value used to differentiate it.

 

[DonkeyKong]

Solution:

SELECT TEMP_HOLD.CUSIP, (SELECT SUM(HOLDERS.ORIGINAL_FACE) 
FROM HOLDERS
WHERE HOLDERS.CUSIP = TEMP_HOLD.CUSIP) AS [400 ORIGINAL FACE], 
(SELECT SUM([300_HOLDERS].ORIGINAL_FACE) 
FROM [300_HOLDERS] 
WHERE [300_HOLDERS].CUSIP = TEMP_HOLD.CUSIP) AS [300 ORIGINAL FACE]
FROM TEMP_HOLD
GROUP BY TEMP_HOLD.CUSIP
ORDER BY TEMP_HOLD.CUSIP

Turns out I was making the problem overly complex... go figure

 

[DonkeyKong]

Plog,

you may be correct in that assumption, however, there are extenuating circumstances which would ultimately make that a much for difficult process. Here are several that I can think of...

1. I am getting the values from linked databases, and they differentiate the data between 300 and 400 databases.

2. Often times you want values for only 1 of the databases.

3. Often times the information is not the same for the same field, thus I would have an account on the 400 that is different from the account on the 300 even though they contain the same original face values... OR the 400 account contains the sum of the original face of the 3 accounts on the 300 side.

4. This would leave me with NULLS in a lot of my primary keys, especially account.

5. There's more, but that oughta do it

**EDIT**

I do see what you're saying I guess and that would not leave me with NULLS but for right now I'm going to leave it. Re-doing the whole table structure is more than I want to do and while this one may be faulty it is operational. Not good edicate but seriously... not going to change EVERYTHING right now. I just finished the update procedure and don't look forward to a rewrite. I will keep this in mind though and may change it at a later date.