count-sum-case-when

select 
 department,category,upc,max(upc_desc) as upc_desc
 --all
 ,count(distinct order_id) as orders
 ,sum(merch_sales)         as sales
 --1094
 ,count( distinct case when store_id=1094 then order_id else null end )   as orders_1094
 ,sum( case when store_id=1094 then merch_sales else 0 end )           as sales_1094
 --ControlStore
 ,count( distinct case when store_id !=1094 then order_id else null end ) as orders_controlstore
 ,sum( case when store_id !=1094 then merch_sales else 0 end )         as sales_controlstore
from testdb.test99_upc_ol
where paid_date >= '2020-01-01' and paid_date <='2020-04-26'
group by department,category,upc

,count(distinct order_id) as orders
,count( distinct case when store_id  =1094 then order_id else null end ) as orders_1094
,count( distinct case when store_id !=1094 then order_id else null end ) as orders_controlstore

,sum(merch_sales) as sales
,sum( case when store_id  =1094 then merch_sales else 0 end ) as sales_1094
,sum( case when store_id !=1094 then merch_sales else 0 end ) as sales_controlstore 

 

 department,
 count(distinct department) as total_department,
 --department
 ,case when department=96 then count(distinct department) else nul end as total_department_96
 ,case when department=92 then count(distinct department) else null end as total_department_92
 ....
 group by department

 

 

select 
 channel
 ,count(distinct order_id) as orders
 ,sum(gmv) as sales
from (
--1
select store_id,channel,order_id,gmv
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date >='2020-05-20' and paid_date <='2020-05-24'
and store_id=1094
--end1
) as aa
group by channel
;
+----------+---------+-------------+
| channel  | orders  |    sales    |
+----------+---------+-------------+
| WMDJ     | 1140    | 148934.78   |
| JDDJ     | 1862    | 209989.17   |
| SNG      | 19709   | 1584130.41  |
+----------+---------+-------------+

 

select 
 store_id
 ,count(distinct case when channel='SNG'  then order_id else null end) as orders_sng
 ,sum(case when channel='SNG'then gmv else 0 end)  as sales_sng 
 ,count(distinct case when channel='WMDJ' then order_id else null end)  as orders_wmdj
 ,sum(case when channel='WMDJ'then gmv else 0 end) as sales_wmdj 
 ,count(distinct case when channel='JDDJ' then order_id else null end) as orders_jddj
 ,sum(case when channel='JDDJ'then gmv else 0 end) as sales_jddj
from (
--1
select store_id,channel,order_id,gmv
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date >='2020-05-20' and paid_date <='2020-05-24'
and store_id=1094
--end1
) as aa
group by store_id 
;
+-----------+-------------+-------------+--------------+-------------+--------------+-------------+
| store_id  | orders_sng  |  sales_sng  | orders_wmdj  | sales_wmdj  | orders_jddj  | sales_jddj  |
+-----------+-------------+-------------+--------------+-------------+--------------+-------------+
| 1094      | 19709       | 1584130.41  | 1140         | 148934.78   | 1862         | 209989.17   |
+-----------+-------------+-------------+--------------+-------------+--------------+-------------+

 

 

select 
 channel
 ,case when channel='SNG'  then count(distinct order_id) else 0 end as orders_sng
 ,case when channel='SNG'  then sum(gmv) else 0 end as sales_sng
 ,case when channel='WMDJ' then count(distinct order_id) else 0 end as orders_wmdj
 ,case when channel='WMDJ' then sum(gmv) else 0 end as sales_wmdj
 ,case when channel='JDDJ' then count(distinct order_id) else 0 end as orders_jddj
 ,case when channel='JDDJ' then sum(gmv) else 0 end as sales_jddj
from (
--1
select store_id,channel,order_id,gmv
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date >='2020-05-20' and paid_date <='2020-05-24'
and store_id=1094
--end1
) as aa
group by channel 
;
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| channel  | orders_sng  |  sales_sng  | orders_wmdj  | sales_wmdj  | orders_jddj  | sales_jddj  |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| WMDJ     | 0           | 0.00        | 1140         | 148934.78   | 0            | 0.00        |
| JDDJ     | 0           | 0.00        | 0            | 0.00        | 1862         | 209989.17   |
| SNG      | 19709       | 1584130.41  | 0            | 0.00        | 0            | 0.00        |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+

select 
 channel
 ,case when channel='SNG'  then count(distinct order_id) else NULL end as orders_sng
 ,case when channel='SNG'  then sum(gmv) else 0 end as sales_sng
 ,case when channel='WMDJ' then count(distinct order_id) else NULL end as orders_wmdj
 ,case when channel='WMDJ' then sum(gmv) else 0 end as sales_wmdj
 ,case when channel='JDDJ' then count(distinct order_id) else NULL end as orders_jddj
 ,case when channel='JDDJ' then sum(gmv) else 0 end as sales_jddj
from (
--1
select store_id,channel,order_id,gmv
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date >='2020-05-20' and paid_date <='2020-05-24'
and store_id=1094
--end1
) as aa
group by channel 
;
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| channel  | orders_sng  |  sales_sng  | orders_wmdj  | sales_wmdj  | orders_jddj  | sales_jddj  |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| WMDJ     | NULL        | 0.00        | 1140         | 148934.78   | NULL         | 0.00        |
| JDDJ     | NULL        | 0.00        | NULL         | 0.00        | 1862         | 209989.17   |
| SNG      | 19709       | 1584130.41  | NULL         | 0.00        | NULL         | 0.00        |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+

 

select 
 channel
 ,count(distinct case when channel='SNG'  then order_id else null end) as orders_sng
 ,sum(case when channel='SNG'then gmv else 0 end)  as sales_sng 
 ,count(distinct case when channel='WMDJ' then order_id else null end)  as orders_wmdj
 ,sum(case when channel='WMDJ'then gmv else 0 end) as sales_wmdj 
 ,count(distinct case when channel='JDDJ' then order_id else null end) as orders_jddj
 ,sum(case when channel='JDDJ'then gmv else 0 end) as sales_jddj
from (
--1
select store_id,channel,order_id,gmv
from cn_ec_bi_dl_secure.wm_order_line_o
where paid_date >='2020-05-20' and paid_date <='2020-05-24'
and store_id=1094
--end1
) as aa
group by channel 
;
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| channel  | orders_sng  |  sales_sng  | orders_wmdj  | sales_wmdj  | orders_jddj  | sales_jddj  |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+
| WMDJ     | 0           | 0.00        | 1140         | 148934.78   | 0            | 0.00        |
| JDDJ     | 0           | 0.00        | 0            | 0.00        | 1862         | 209989.17   |
| SNG      | 19709       | 1584130.41  | 0            | 0.00        | 0            | 0.00        |
+----------+-------------+-------------+--------------+-------------+--------------+-------------+