combine two rows with 2 months into one row of one month, containing null values into one-CodePudding

I would like to have a dataframe where 1 row only contains one month of data.

month           cust_id closed_deals cum_closed_deals checkout cum_checkout
2019-10-01          1       15           15            null        null
2019-10-01          1       null         15            210         210
2019-11-01          1       27           42            null        210
2019-11-01          1       null         42            369         579

Expected result:

month           cust_id closed_deals cum_closed_deals checkout cum_checkout
    2019-10-01          1       15           15          210        210
    2019-11-01          1       27           42          369        579

At first, I thought a normal groupby will work, but as I try to group by only by "month" and "cust_id", I got an error saying that closed_deals and checkout also need to be in the groupby.

CodePudding user response：

You may simply aggregate by the (first of the) month and cust_id and take the max of all other columns:

SELECT
    month,
    cust_id,
    MAX(closed_deals) AS closed_deals,
    MAX(cum_closed_deals) AS cum_closed_deals,
    MAX(checkout) AS checkout,
    MAX(cum_checkout) AS cum_checkout
FROM yourTable
GROUP BY
    month,
    cust_id;