Sql Query

Hi

I've come unstuck and would greatly appreciate some help!

I'm using MySql and  have a table with sales, bonus, date and salesperson columns

I want to tot up all the sales per sales person and remove the bonus if the total sales for that sales person is less than 1000.

There are thousands of entries but in essence as below:-


Sales   Bonus   Date                 SalesPerson
23.00    0         01-01-2011      Smith
21.00    0         01-02-2011      Smith
27.00    0         01-03-2011      Smith
0           20.00  01-06-2011      Smith
23.00    0         01-01-2011      Jones
21.00    0         01-02-2011      Jones
27.00    0         01-03-2011      Jones
0           20.00  01-06-2011      Jones

TIA

Grant
Buzby58Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can start this query to find those that shall be affected:
select year(date), Salesperson, sum(sales), sum(bonus)
  from yourtable
 group by  year(date), Salesperson
 having sum(Sales) <= 1000
  and sum(bonus) > 0

Open in new window


and then, based on that output, do the update.
depends on how you want to "run" this update, it could be a stored procedure etc ...

0
sachinpatil10dCommented:
try this

select salesperson , case when SUM(sales) < 1000 then SUM(sales) else SUM(sales) +SUM(bonus) end tot
from yourTableName
group by salesperson

Open in new window

0
Buzby58Author Commented:
Thanks for the prompt replies - sorry - I've not made myself clear

I want to add up all the total sales per salesperson.

If the total sales are less than 1000 then delete the bonus payment row made on a certain date for that sales person

ie:

add up total sales per salesperson

If sales for that salesperson < 1000

delete from bonus row where date is 01-06-2011

Many thanks!

Grant
0
sachinpatil10dCommented:
Try this

delete from yourTableName where month(date) in  (select month(date)
from yourTableName
group by  month(date)
having sum(Sales) <= 1000)
and Bonus> 0

Open in new window

or
delete from yourTableName where year(date) in  (select year(date)
from yourTableName
group by  year(date)
having sum(Sales) <= 1000)
and Bonus> 0

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Buzby58Author Commented:
Thanks very much - I see where I was going wrong now!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.