Unique & Rept Customer Sales based on date-CodePudding

I am new to Mysql, I have the data like this,

Order_dt    User_ID Sales
28-03-2022  PRPK-12 84
28-03-2022  PRPK-11 41
28-03-2022  PRPK-10 55
28-03-2022  PRPK-12 76
26-03-2022  PRPK-10 54
27-03-2022  PRPK-11 88
27-03-2022  PRPK-11 51
27-03-2022  PRPK-10 40 
27-03-2022  PRPK-10 40

& I need o/p like below the format.

Order_date  Unique_Cx   Unique_Cx_Sales Rept_Cx Rept_Cx_Sales
26-03-2022  1   54  0   0
27-03-2022  0   0   2   219
28-03-2022  2   96  1   160

I'm getting only unique & Rept count by using the 'Having' function, but im unable to map with the sales. Kindly help. Thanking you.

CodePudding user response：

First group by dt and user id in sub query then use conditional aggregation

select order_dt,
        sum(case when cd = 1 then 1 else 0 end) unique_cx,
        sum(case when cd = 1 then sales else 0 end) unique_cx_sales,
        sum(case when cd > 1 then 1 else 0 end) repeat_cx,
        sum(case when cd > 1 then sales else 0 end) repeat_cx_sales
from       
(
select   order_dt, user_id,
    count(*) cd,sum(sales) sales
from t
group by order_dt,user_id
) s
group by order_dt
;