Results 1 to 6 of 6

Thread: Select Query

  1. #1
    Join Date
    Dec 2002
    Location
    Nepal
    Posts
    32

    Unanswered: Select Query

    I have one table i need to just select for one month data till the sum of cost filed 5000.

    select sum(cost) as R3c, min(id_r), max(id_r) from pro where
    dt >= '2010-11-01 00:00:00' and dt <= '2010-11-31 23:59:59'
    and sum(cost) <= 5000;

    It give error Invalid Group By function.

    Please smuggest how i can achieve this.

    thank you

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at SELECT .. GROUP BY and HAVING. For example

    SELECT deptid, SUM(salary)
    FROM depts
    GROUP BY deptid
    HAVING SUM(salary) > 1000;

    After re-reading your post I am not sure whether you are looking to SUM(cost) where cost <= 5000?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2002
    Location
    Nepal
    Posts
    32
    I am trying very simple query. I need to keep sum(cost) <= 5000 for a month data in one table and delete rest records. Can you please give a simple sql for this.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not following what you are looking for? Can you provide a small example of what you are looking to select? I think what you are looking for is a runtime aggregator so that once the costs exceed 5000 you stop. Is that what you are looking for?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Dec 2002
    Location
    Nepal
    Posts
    32
    Hi,
    I have one reseller he need to adjust the sum of cost 5000 only. now his account have more than 30,000 he ask us to delete the records so the sum should come only 5000.

    select sum(cost) as TotalMonth_cost from tr_table where p_dt >= '2010-09-01 00:00:00' and p_dt <= '2010-09-31 23:59:59'

    if i run this the total TotalMonth_cost will be 30,000, Now i need to delete some rows to adjust TotalMonth_cost only 5000.

    This is what i am looking.

    BR,

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try something like this:

    mysql> set @total := 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select id, salary, @total:=salary+@total from info;
    +----+--------+-----------------------+
    | id | salary | @total:=salary+@total |
    +----+--------+-----------------------+
    | 1 | 1 | 1 |
    | 2 | 3 | 4 |
    | 3 | 3 | 7 |
    | 4 | 8 | 15 |
    +----+--------+-----------------------+
    4 rows in set (0.00 sec)

    mysql> select id, salary, total from (select id, salary, @total:=salary+@total as total from info) a where a.total < 10;
    +----+--------+-------+
    | id | salary | total |
    +----+--------+-------+
    | 1 | 1 | 1 |
    | 2 | 3 | 4 |
    | 3 | 3 | 7 |
    +----+--------+-------+
    3 rows in set (0.01 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •