Sql Query

Posted on 2011-10-21
Last Modified: 2012-08-14

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


Question by:Buzby58
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 ...

    LVL 9

    Expert Comment

    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


    Author Comment

    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


    add up total sales per salesperson

    If sales for that salesperson < 1000

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

    Many thanks!

    LVL 9

    Accepted Solution

    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

    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


    Author Closing Comment

    Thanks very much - I see where I was going wrong now!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now