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.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2010-12-18 : 10:41:08
|
| I have two tables.Table 1table1.sopnumbe, table1.subtotalTable 2table2.sopnumbe, table2.itemnmbr, table2.xtndprce(multiple records in table2 for 1 record in table1)I am trying to determine if the sum of xtndprce in table2 equals the subtotal value in table1.I ran this query -select * from table1 where table1.subtotal <> sum(table2.XTNDPRCE) fromtable1 left outer join table2 on table1.sopnumbe = table2.sopnumbewhere table1.sopnumbe like 'POR%'SQL returns this statement -Server: Msg 147, Level 15, State 1, Line 1An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.I eventually will want to update table1 with the totals from table2 per sopnumbe. How can I change my query to get a valid dataset and then eventually update the data?Thank in advance...... |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-18 : 11:06:26
|
| [code]SELECT t1.sopnumbe, t1.subtotal, x.xtndprceFROM Table1 t1JOIN ( SELECT sopnumbe, sum(xtndprce) xtndprce FROM table2 WHERE sopnumbe like 'por%' GROUP BY sopnumbe) xOn x.sopnumbe = t1.sopnumbeWHERE t1.sopnumbe like 'por%'[/code] |
 |
|
|
btamulis
Yak Posting Veteran
64 Posts |
Posted - 2010-12-18 : 12:05:17
|
| Thank you Russell - I'm a newbie and I always have trouble with alias and nested queries. Your query worked great and i was able to modify so I could update the records.Thanks a bunch - this forum is great. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-18 : 20:14:13
|
| You're very welcome. Glad it worked out for you. We were all newbies at one time. Keep coming back when you have questions. |
|
|
|
|
|
|
Join Problem