MYSQL Select Distinct with SUM

distinctgroup byMySQLselect

I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:

----------------------------------------
| stock_key | user_key | units | price |
----------------------------------------
| fund_a    | user_1   | 1000  | 100.35|
| fund_c    | user_2   | 4000  | 101.55|
| fund_d    | user_3   | 2000  | 105.65|
| fund_a    | user_1   | 1500  | 101.45|
| fund_b    | user_1   | 500   | 103.35|
----------------------------------------

I wish to select the portfolio of user_1 with distinct stock_key. Plus I need the price as the aggregate total paid over the number of units. For example:

For user_1 the result should be:

[ 
    {fund_key: "fund_a", units: sum(1000,1500), price: sum(1000*100.35,1500*101.45)/sum(1000,1500)},
    {fund_key: "fund_b", units: 500, price: 103.35}
]

This is important because once the user has brought more units of the same stock at a lower/higher price than before, the accumulated stocks price is averaged out.

Best Answer

Here's your query:

    SELECT 
        concat("fund_key: ",stock_key,", units: ",sum(units),", price: ",round(sum(units*price)/sum(units),2)) as result
    FROM PRUEBA.new_table2
    GROUP BY stock_key,user_key;

Result:

enter image description here

Related Solutions

Mysql – 2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data.

e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice. However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice.

One way you could do it is instead of querying the "test" table directly, calculate some of the aggregates beforehand:

Select mid,
COUNT(distinct pid) as cpid,
SUM(nwgh) as totalnwgh,
SUM(owgh) as totowgh    -- I added this one
from 
    (select mid, pid, sum(distinct owgh) as owgh, sum(nwgh) as nwgh from test group by mid, pid) as A
GROUP BY mid

Sql-server – Change SELECT DISTINCT to UPDATE DISTINCT

To just update an arbitrary one from each distinct group you could use

WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number] 
                                       ORDER BY (SELECT 0)) AS RN,
                [Processing_Result_Text],
                [Processing_Result]
         FROM   [InterfaceInfor].[dbo].[ProjectMaster]
         WHERE  NOT EXISTS (SELECT *
                            FROM   [IMS].[dbo].[THEOPTION]
                            WHERE  [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] = [IMS].[dbo].[THEOPTION].[NAME]))
UPDATE T
SET    [Processing_Result_Text] = 'UNIQUE',
       [Processing_Result] = 0
WHERE  RN = 1;

If you decide there is some criteria by which the row to be updated should be selected after all simply change the ORDER BY (SELECT 0) accordingly so that the desired target row is ordered first - e.g. ORDER BY DateInserted desc would update the latest one as ordered by a column called DateInserted if such a column exists.

This uses a common table expression (CTE) because it is not permitted to reference ranking functions such as ROW_NUMBER directly in the WHERE clause. It is allowed to update data via common table expressions in the same circumstances as for updatable views (basically the data being updated must be able to be mapped back straight forwardly to specific items in a single base table).

If you are not yet familiar with ranking functions you may well find SELECT-ing from the CTE first to be beneficial.

CREATE TABLE #TheOption(NAME VARCHAR(50));

CREATE TABLE #ProjectMaster
(
Finance_Project_Number VARCHAR(10) NOT NULL,
Processing_Result_Text VARCHAR(50) NULL,
Processing_Result INT NULL
);

INSERT INTO #ProjectMaster (Finance_Project_Number, Processing_Result_Text)
VALUES ('A00001', 'A'), 
       ('A00001', 'B'), 
       ('A00001', 'C'), 
       ('B99999', 'D'), 
       ('B99999', 'E'), 
       ('C47474', 'F'), 
       ('C47474', 'G');

INSERT INTO #TheOption (NAME) VALUES('C47474');


WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Finance_Project_Number 
                                       ORDER BY (SELECT 0)) AS RN,
                Finance_Project_Number,
                Processing_Result_Text,
                Processing_Result
         FROM   #ProjectMaster pm
         WHERE  NOT EXISTS (SELECT *
                            FROM   #TheOption opt
                            WHERE  pm.Finance_Project_Number =opt.NAME))
SELECT *
FROM T
ORDER BY Finance_Project_Number, RN;

Example results are below

The C47474 rows are filtered out as they do exist in the other table so don't meet the NOT EXISTS, the remaining rows are grouped by Finance_Project_Number and assigned a sequential number within each group.

In this case the yellow rows would meet the RN = 1 condition and be updated. However there is no guarantee exactly how these numbers will be assigned within each group unless you use a ORDER BY clause on an expression guaranteed to be unique. Without this it could potentially change even between successive executions of the same statement.

Best Answer

Related Solutions

Mysql – 2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Sql-server – Change SELECT DISTINCT to UPDATE DISTINCT

Related Question