Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql Query

Posted on 2011-10-21
5
Medium Priority
?
256 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Buzby58
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37005357
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37005366
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
 

Author Comment

by:Buzby58
ID: 37005466
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
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 2000 total points
ID: 37005579
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
 

Author Closing Comment

by:Buzby58
ID: 37005591
Thanks very much - I see where I was going wrong now!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question