Selecting all values - 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

General SQL Server Forums

New to SQL Server Programming

Selecting all values

Author

Topic

HCsquared
Starting Member

1 Post

Posted - 2011-08-15 : 13:36:57

Hi there, I've ran into a little SQL problem and I just can't figure it out, so I found this site! I'm very new to SQL and only know the basic statements, so any help would be greatly appreciated.

Here are two simple tables:
SQL_TABLE:
DOC_ID---Quantity---Weight <--- SQL server's table
1002------100--------55
1002------250--------100
1003------111--------1000
1003------111--------400

APP_TABLE:
SHIP_ID---QTY <--- Application's table (it doesn't have weight)
1002------100
1002------250
1003------111
1003------111

I'm trying to populate my shipping form so I don't have to manually enter the total weight (Each DOC_ID represents 1 shipping form, so ID 1002 is one form, and 1003 is another). But the thing is the application table doesn't have weights, so I have to pull it from the SQL table.

I wrote this query: SELECT Weight FROM SQL_TABLE WHERE SHIP_ID = :DOC_ID AND QTY = :Quantity
Then I'll just sum the weights.

It works fine a nice until there's a case where SHIP_ID and QTY all contain the same values. For example, ID 1002 works perfectly and it gives me a total weight of 155. But, ID 1003 doesn't work and it gives me a total weight of 2000. I know it's because the quantities and ID's are the same, so it's counting the 1000 weight twice.

So I guess my question is, is there a way for me to calculate the total sum of weights so the total of 1400 shows up?

Thanks very much.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 16:24:40

Well dear I am not sure what you're doing on the form level. However considering the example and using the table: SQL_TABLE, you can achieve what you want via following query

SELECT Sum(Weight)
FROM SQL_TABLE
WHERE SHIP_ID = :DOC_ID AND QTY = :Quantity
GROUP BY SHIP_ID,QTY

This is a "SQL server" forum.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources