CODEWITHSUNDEEP
mysqlgroup-by
Mike Athi
Mike Athi

Reputation: 23

MySQL: GROUP BY and fill empty cells with value from next row from this group

I have a table with products and multiple categories (more than 2), similar to this one:

| category1 | category2 |  product  | option |
|  winter   |           |  goodyear |    1   |
|  winter   |           |  goodyear |    2   |
|           |  summer   |  goodyear |    1   |
|           |  summer   |  goodyear |    2   |

What I would like to achieve is to GROUP BY 'product' and by 'option', but at the same time fill the empty cells with the content of the next row from this group. The result I am looking to get is:

| category1 | category2 |  product  | option | count|
|  winter   |  summer   |  goodyear |    1   |   2  |
|  winter   |  summer   |  goodyear |    2   |   2  |

Is it possible to get such result with a MySQL query?

Upvotes: 0

Views: 476

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

Isn't this simply:

select
  max(category1) as category1,
  max(category2) as category2,
  product,
  option,
  count(*)
from mytable
group by product, option;

With an aggregation (GROUP BY) you must always specify how to aggregate the other values you are selecting (e.g. whether you want the maximum or the minimum value etc.). Both MAX and MIN ignore nulls, so you get the value you are interested in.

Upvotes: 2

Related Questions