CODEWITHSUNDEEP
mysqlsql
Hemant
Hemant

Reputation: 205

limiting the rows to where the sum a column equals a certain value in MySQL

I want to write a query which returns all rows until the sum of one of the columns value reaches a certain value.

For example in the table below:

           DATE             ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4
    2013-02-03 00:00:00    78927        6
    2013-02-04 00:00:00    85662        2
    2013-03-05 00:00:00    47978        1
    2013-08-07 00:00:00     8582        1

If I want to get the rows until the sum of column Meeting equals 7.

           DATE             ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4

If I want to get the rows until the sum of column Meeting equals 13.

     DATE                   ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4
    2013-02-03 00:00:00    78927        6

Upvotes: 14

Views: 22697

Answers (8)

valdas.mistolis
valdas.mistolis

Reputation: 1341

This one outperforms all.

SET @runningTotal=0;
SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  @runningTotal + O.MyAmountCol as 'RunningTotal',
  @runningTotal := @runningTotal + O.MyAmountCol
FROM Table1 O
HAVING RunningTotal <=7;

SQL Fiddle

Take a look at execution plans for both queries.

Upvotes: 6

user330315
user330315

Reputation:

As no DBMS is given, the following is an ANSI SQL solution which works on a wide range of DBMS:

select *
from (
    select date_column, 
           etc, 
           sum(Meeting) over (order by date_column asc) run_sum
    from meetings
) t
where run_sum <= 7
order by date_column asc;

(I used the column name date_column instead of date because DATE is a reserved word in SQL and should not be used as a column name to avoid problems)

Upvotes: 1

DMK
DMK

Reputation: 2527

Here's a way which should work in MySQL :

SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  (SELECT
     sum(MyAmountCol) FROM Table1
   WHERE Id <= O.Id) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <= 7

It involves calculating a running total and selecting records while the running total is less than or equal to the given number, in this case 7.

SQL Fiddle

Upvotes: 9

toomanyredirects
toomanyredirects

Reputation: 2002

Here's a way to do it without a stored procedure:

SET @msum := 0;
SELECT t1.* 
FROM (
    SELECT m.*,  
          (@msum := @msum + m.meetings) AS cumulative_meetings
    FROM meetings m 
    ORDER BY m.date ASC
) t1 
WHERE t1.cumulative_meetings <= 7;

Upvotes: 9

Lloyd Santos
Lloyd Santos

Reputation: 402

In Oracle, I think you can use the SUM analytic function. You can refer here: http://www.adp-gmbh.ch/ora/sql/analytical/sum.html

Upvotes: 0

Manolis
Manolis

Reputation: 738

Hemant you do not state the RDBMS that use. Here is a script in t-sql that you can use in order to solve your problem. 

DECLARE @numberToReach INT;
SET @numberToReach = 10; --you can change this

DECLARE @date DATETIME;
DECLARE @etc VARCHAR(20);
DECLARE @meeting INT;
DECLARE @temp_sum INT;

CREATE TABLE #tempTable
    (
        Dates DATETIME,
        Etcs VARCHAR(20),
        Meeting INT,
    )

DECLARE tempcursor CURSOR FOR
        SELECT *
        FROM YourTABLENAME
OPEN tempcursor;
FETCH NEXT FROM tempcursor INTO @date, @etc, @meeting;

WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @temp_sum = @temp_sum + @meeting;
    IF @temp_sum < @numberToReach 
    BEGIN
        INSERT INTO #tempTable
        (
            Dates,
            Etcs,
            Meeting
        )
        VALUES
        (
            @date, 
            @etc, 
            @meeting
        )

        FETCH NEXT FROM tempcursor INTO @date, @etc, @meeting;
    END 
END

SELECT * FROM #tempTable

CLOSE tempcursor
DEALLOCATE tempcursor

DROP TABLE  #tempTable

Upvotes: 1

IamIC
IamIC

Reputation: 18239

If you are using SQL Server, then use CROSS APPLY.

Upvotes: -1

lc.
lc.

Reputation: 116478

Here's an ugly way:

SELECT *
FROM meetings m1
WHERE (SELECT SUM(m2.Meeting) 
       FROM meetings m2 
       WHERE m2.DATE < m1.DATE OR (m2.DATE = m1.DATE AND m2.ETC >= m1.ETC)) <= 7

The ordering is based on DATE first, then ETC in descending order, since that seems to be what we have to go on. Note that if that is not unique, you will get the wrong result.

Upvotes: 0

Related Questions