How to GROUP and return a value based on the oldest date?-CodePudding

So I have this table:

  n    |   id_product     |   name    |  price  |   quantity   |   created_at
 -------------------------------------------------------------------------------
  1         1da05150         pencil      800          10          31-12-2021
  2         1da05150         pencil      700          7           15-01-2022
  3         1da05150         pencil      1500         13          20-01-2022
  4         510eac00         book        350          25          29-12-2021

I'd like to group them by ID_PRODUCT column. On the columns i'd like on return would be ID_PRODUCT, NAME, sum of quantities by id_product, and for the PRICE column i'd like to obtain the price value that comes from the oldest product (i'd like it to consider the CREATED_AT column for the price purpose)

On the result should be like this:

  n    |   id_product     |   name    |  price  |   quantity   |   created_at
 -------------------------------------------------------------------------------
  1         1da05150         pencil      800          30          31-12-2021
  2         510eac00         book        350          25          29-12-2021

CodePudding user response：

you need Subquery to join table with itself your data

create temporary table tmp_a (
   n          int
  ,id_product VARCHAR(30)
  ,name       VARCHAR(30)
  ,price      int
  ,quantity   int
  ,created_at date
);

INSERT INTO tmp_a
(n,id_product,name,price,quantity,created_at)
VALUES (1,'1da05150','pencil',800,10,'12-31-2021'),
(2,'1da05150','pencil',700,7,'01-15-2022'),
(3,'1da05150','pencil',1500,13,'01-20-2022'),
(4,'510eac00','book',350,25,'12-29-2021');

your query

SELECT t1.n,
       t1.id_product,
       t1.NAME,
       t1.price,
       t2.quantity,
       t2.created_at
FROM   tmp_a t1
       JOIN (SELECT id_product,
                    NAME,
                    Sum(quantity)   quantity,
                    Min(created_at) created_at
             FROM   tmp_a
             GROUP  BY id_product,
                       NAME) t2
         ON t1.id_product = t2.id_product
            AND t1.created_at = t2.created_at