CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Nigel
Nigel

Reputation: 35

SQL - Count Number of Records with Combined Sum

I have a User table similar to this:

|  ID  |  DONATION  |   RANK   |
|  4   |        10  |     12   |
|  9   |        20  |      8   |
|  2   |        40  |      5   |
|  3   |        80  |      2   |

I would like to construct a query where I retrieve the number of users where the combined sum of the donations is less than say 100, ordered by rank... something like (obviously not correct):

select count(*) where sum(donation) < 100 order by rank

Can't figure out how to do this if it's even possible. Not even sure I'm going about this the right way. Any ideas or pointers?

Upvotes: 1

Views: 2413

Answers (3)

Scott
Scott

Reputation: 150

Assuming you have the following Donation table (as much as I think your ID column points to a user, I could not be sure):

| ID | UserId | Donation |
| 1  | 1      | 60       |
| 2  | 1      | 35       |
| 3  | 2      | 70       |
| 4  | 2      | 40       |
| 5  | 3      | 90       |


The the following query will give you the number of users with their total donation < 100 

SELECT COUNT(1)
FROM (
    SELECT  UserId
    FROM    Donations 
    GROUP BY UserId
    HAVING SUM(Donation) < 100
) AS t

Update based on extra information.
I do not believe you can get what you want from a single SQL statement, what you would need to do is create a cursor that loops around the records you want (in appropiate order) inserting the id into a temp table, and maintaining a total of the donations. When the total is above your limit, break out of the cursor loop and return the results. Something like this:

CREATE PROCEDURE GetTopDontations( limit DECIMAL(...) )
BEGIN
    CREATE TEMPORARY TABLE t1 (
        id INT
    );

    DECLARE total, Donation DECIMAL(...);
    DECLARE Id INT;
    DECLARE curDonations CURSOR FOR SELECT ID, Dontaion FROM Donations ORDER BY <something relevant>;

    SET total = 0.0;
    OPEN curDonations;
    read_loop: LOOP
        FETCH curDonations INTO Id, Donation;
        INSERT INTO t1( id ) VALUES ( Id );
        SET total = total + Donation;
        IF( total > limit ) THEN
            LEAVE read_loop;
        END IF;
    END LOOP:
    CLOSE curDonations;

    SELECT d.*
    FROM    Donations d
        INNER JOIN t1
            ON d.Id = t1.Id;
END;

Upvotes: 2

Vincent Savard
Vincent Savard

Reputation: 35927

WHERE only works for columns or expressions, if you want to filter with an aggregate function (like SUM), you have to use HAVING. For example :

  SELECT COUNT(*)
    FROM your_table
  HAVING SUM(donation) < 100

This query doesn't really make sense without a valid GROUP BY, but you get the point: use HAVING to filter with an aggregate function. Technically, you should group by user id then do the count, and sum the count.

Upvotes: 6

Brian Willis
Brian Willis

Reputation: 23894

SELECT id, sum(donation) AS totalDonation 
FROM TableName
GROUP BY id
HAVING totalDonation < 100

Upvotes: 0

Related Questions