Home » SQL & PL/SQL » SQL & PL/SQL » select rows included in a sum
select rows included in a sum [message #206860] Fri, 01 December 2006 15:21 Go to next message
jemkeith
Messages: 26
Registered: May 2006
Junior Member
I have some data including county, names, addresses and assessment data. A person can have several different records in a county. A person will only get a bill if the sum of their assessment in a county is $1000 or over. I can select which person should be getting a bill by
select county, name, address, city, state, zip, sum(assessment) from assessment_data
group by county, name, address, city, state, zip
having sum(assessment) > 1000


What I need to do is update the table flagging each record that should be getting a bill, or setting a flag on each record that is included in the sum. I really can't think of a simple, reliable solution to this.

[Updated on: Fri, 01 December 2006 15:21]

Report message to a moderator

Re: select rows included in a sum [message #207084 is a reply to message #206860] Mon, 04 December 2006 02:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you not do something like:
UPDATE assessment_data
set flag = 'Y' WHERE (county, name, address, city, state, zip) IN (select county, name, address, city, state, zip from assessment_data
group by county, name, address, city, state, zip
having sum(assessment) > 1000);
Re: select rows included in a sum [message #207170 is a reply to message #207084] Mon, 04 December 2006 07:02 Go to previous message
jemkeith
Messages: 26
Registered: May 2006
Junior Member
Yep, that's exactly what I needed. I just couldn't think of it!!! Thanks!!!!
Previous Topic: droping the queue tables
Next Topic: Converting rows into column
Goto Forum:
  


Current Time: Wed Dec 07 14:38:03 CST 2016

Total time taken to generate the page: 0.12101 seconds