Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find the minimum of a sum of values based on a distinct value?

Using the table below, I’ve been trying to figure out how I can find the smallest purchase amount based on a unique ID.

ID  |  UNITS_BOUGHT  |  UNIT_PRICE
----------------------------------
 1  |       1        |    10.99
 1  |       3        |    3.49
 1  |       2        |    4.99
 2  |       1        |    124.99
 2  |       1        |    4.99
 3  |       5        |    9.99
 3  |       3        |    4.99

What I need to do is get the total number of distinct IDs first, then find the smallest purchase amount amongst the distinct IDs. The purchase amount is found by taking UNITS_BOUGHT, then multiplying it by UNIT_PRICE, then summing the values for each ID. So, in this case, the total distinct IDs would 3, and the smallest purchase amount would be 31.44, the amount for ID 1.

IDs  |  MIN_PURCHASE
--------------------
 3   |     31.44

What I haven’t been able to figure is how to display that smallest purchase amount. This is the current code I have:

SELECT COUNT(DISTINCT ID) AS 'IDs',
ROUND(MIN(SUM(UNITS_BOUGHT * UNIT_PRICE) = (
    SELECT UNITS_BOUGHT * UNIT_PRICE
    FROM TABLE
    GROUP BY ID
)), 2) AS 'MIN_PURCHASE'
FROM TABLE;

The code above does provide an "invalid use of group function error", and I’m not entirely sure as to why that is.

Tags:

2

Answers


  1. 0

    SELECT MIN(SUM(UNITS_BOUGHT * UNIT_PRICE))
    FROM TABLE
    GROUP BY ID;

    Login or Signup to reply.
  2. 0

    You might wanna use a CTE.

    WITH CTE AS (
    SELECT ID, SUM(UNITS_BOUGHT * UNIT_PRICE) AS TotalPurchase
    FROM TABLE
    GROUP BY ID
)
SELECT 
    (SELECT COUNT(DISTINCT ID) FROM YourTableName) AS IDs,
    ROUND(MIN(TotalPurchase), 2) AS MIN_PURCHASE
FROM CTE
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search