Argthtjtr

This has been bugging me for some time

table : stock

|   customerID   | Item  |  Quantity  |  Price  |

|     cus1       |  A    |     3      |    4    |

|     cus1       |  B    |     2      |    3    |

|     cus1       |  C    |     3      |    3    |

|     cus2       |  A    |     3      |    3.50 |

|     cus2       |  C    |     3      |    2    |

|     cus3       |  D    |     6      |    4    |

how to make my mysql query generate view like this below

view : stock_v

| cusID  | Item |Quan |Price |Tot_Qua | Tot_pri | sumtot_pri/tot_qua|

|  cus1  |  A   |  3  | 4    |   8    | 12      | 3.375             | 

|  cus1  |  B   |  2  | 3    |   8    | 6       | 3.375             | 

|  cus1  |  C   |  3  | 3    |   8    | 9       | 3.375             |

|  cus2  |  A   |  3  | 3.50 |   6    | 10.05   | 2.675             |

|  cus2  |  C   |  3  | 2    |   6    | 6       | 2.675             | 

|  cus3  |  D   |  6  | 4    |   6    | 24      | 4.00              |

Example for cus1.
Cus1 have 3 item which is A,B and C.
so I want some formula for

Tot_Qua = 3+2+3 = 8

Tot_pri = price x quan

and Tot_pri must sum for this cus1 record,

sumtot_pri = (quan x price) + (quan x price) + (quan x price)

sumtot_pri = (12) + (6) + (9)

because cus1 have 3 item, and last one

sumtot_pri / Tot_qua = 27 / 8 = 3.375

Need group by I think because I want to see their item. I don't care if column tot_qua and column sumtot_pri/tot_qua will duplicate the same data for each Cus.

In MySQL 8+ you simply use window functions:

select s.*,

       sum(quan) over (partition by cusid) as tot_quan,

       (quan * price) as tot_price,

       sum(quan * price) over (partition by cusid) / sum(quan) over (partition by cusid) as ratio

from stock s;

In earlier versions, you would use subqueries or a similar mechanism. Probably join and group by is simplest:

select s.*, s2.tot_quan, (s.quan * s.price) as tot_price,

       tot_tot_price / tot_quan as ratio

from stock s join

     (select cusid, sum(quan) as tot_quan,

            sum(quan * price) as tot_tot_price

      from stock s2

      group by cusid

     ) s2

     using (cusid);

This query will give you the results you want. It performs all the aggregation you need in a subquery then JOINs that to the main table to give the desired output:

SELECT s.customerID

     , Item

     , Quantity

     , Price

     , Tot_Qua

     , Quantity * Price AS Tot_pri

     , Avg_Pri

FROM stock s

JOIN (SELECT customerID

           , SUM(Quantity) AS Tot_Qua

           , SUM(Quantity * Price) / SUM(Quantity) AS Avg_Pri

      FROM stock

      GROUP BY customerID) s1

    ON s1.customerID = s.customerID

Output:

customerID  Item    Quantity    Price   Tot_Qua     Tot_pri     Avg_Pri

cus1        A       3           4       8           12          3.375

cus1        B       2           3       8           6           3.375

cus1        C       3           3       8           9           3.375

cus2        A       3           3.5     6           10.5        2.75

cus2        C       3           2       6           6           2.75

cus3        D       6           4       6           24          4

Demo on dbfiddle

select  customerID, item, quantity, price,

            (select sum(quantity) from stock s2 where stock.customerID = s2.customerID ) as tot_qua,

            price * quantity as tot_pri,

            (select sum(quantity * price) / sum(quantity) from stock s2 where stock.customerID = s2.customerID) as `sumtotpri/tot_qua`

from stock

order by stock.customerID, stock.item;