PieCloudDB Database SUM a column base on unique values of another column

Suppose I have a table that looks like so:

ID	Quantity	Record
111	8	ae8
111	1	aac
111	8	ae8
111	28	acg
111	9	bbb
128	3	pie
128	3	pie

I want to group this by ID then SUM the Quantity but only by the unique Record values. So that my end result looks like this:

ID	Quantity
111	46
128	3

Solution

You can achieve the desired result by using SQL's GROUP BY clause along with SUM and DISTINCT functions to calculate the sum of unique quantities for each ID. Here's the SQL query you can use:

SELECT ID, SUM(Quantity) AS Quantity
FROM (
    SELECT DISTINCT ID, Record, Quantity
    FROM YourTableName
) AS Subquery
GROUP BY ID;

Replace YourTableName with the actual name of your table. This query first calculates the sum of quantities for each unique combination of ID and Record using a subquery. Then, it groups the results of the subquery by ID to calculate the sum of unique quantities for each ID.

Applying this query to your sample data would yield the desired result.