How to get minimum value of sum from tables-CodePudding

I have this membertype table:

| member_id | member_name |
|-----------|-------------|
| 901       | Liz         |
| 902       | Margaret    |
| 903       | Bob         |

and this revenue table:

| GIS_ID | Member_ID | Total_Revenue | Total_Discount | Number_Transaction |
|--------|-----------|---------------|----------------|--------------------|
| 1001   | 901       | 24,290,288    | 12,894,668     | 266,534            |
| 1001   | 902       | 522,922       | 252,436        | 3,963              |
| 1001   | 903       | 1,581,487     | 847,775        | 19,949             |
| 1002   | 901       | 475,306,161   | 261,595,973    | 4,548,597          |
| 1002   | 902       | 7,194,315     | 3,543,723      | 92,303             |
| 1002   | 903       | 12,293,012    | 3,830,069      | 65,159             |
| 1003   | 901       | 186,170       | 67,250         | 992                |
| 1003   | 902       | 711,765       | 257,145        | 6,195              |
| 1003   | 903       | 109,095       | 37,031         | 1,238              |
| 1004   | 901       | 525,795       | 212,636        | 4,107              |
| 1004   | 902       | 3,545,303     | 1,844,247      | 39,762             |
| 1004   | 903       | 5,153,924     | 1,881,473      | 33,497             |
| 1005   | 901       | 71,538        | 38,579         | 916                |

I want to select which member has the minimum sum value of total discount and its corresponding total discount value. This is what I got (total discount) if I just query their sum value of total discount.

SELECT SUM(revenue.total_discount) as 'Total Discount', membertype.member_id 
FROM revenue 
INNER JOIN membertype on membertype.member_id = revenue.member_id 
GROUP by membertype.member_id;

| Total Discount | member_id |
|----------------|-----------|
| 274809106      | 901       | 
| 5897551        | 902       | 
| 6596348        | 903       |

I want the total discount data for member 902 to be selected (has the minimum sum value of total discount). I've tried this query:

SELECT MIN(SUM(revenue.total_discount)) as 'Total Discount', membertype.member_id
FROM revenue
INNER JOIN membertype on membertype.member_id = revenue.member_id;

but this produces an error:

#1111 - Invalid use of group function

What query should I use to solve this problem?

CodePudding user response：

You can use analytic function RANK() in order to resolve the task and you can try the following SQL query:

SELECT member_id
     , sum_total_discount
 FROM (SELECT SUM(total_discount) as sum_total_discount
            , member_id
            , RANK() OVER (ORDER BY SUM(total_discount) ASC) as rank_num
         FROM membertype m
         JOIN revenue mt 
           ON m.member = mt.Member_ID
        GROUP BY member_id) mm
 WHERE mm.rank_num = 1

dbfiddle

CodePudding user response：

Would something like this work for you or do you exclusively want to achieve this in one main query? Wrapping the query shouldn't matter a lot.

SELECT 'Total Discount', member_id FROM (
    SELECT SUM(revenue.total_discount) as 'Total Discount', membertype.member_id 
    FROM revenue 
    INNER JOIN membertype on membertype.member_id = revenue.member_id 
    GROUP by membertype.member_id
) AS a
ORDER BY 'Total Discount' ASC
LIMIT 1