如何SUM() SQL roq\w

Question

我试过了：

select DISTINCT(campaign_name), count(DISTINCT id) as account_level,
    (SELECT MAX(v) FROM 
        (VALUES (point_reward1) , (point_reward2) , (point_reward3) , (point_reward4) , (point_reward5) , (point_reward6) , (point_reward7) , (point_reward8)) max_point(v))
    AS total_point
from reward_redemption
where
campaign_status !='REJECT_CAMPAIGN'
and is_reported = 0
and campaign_status is not null
AND campaign_name = 'Rwdrdm201049'
group by campaign_name
, point_reward1, point_reward2, point_reward3, point_reward4, point_reward5, point_reward6, point_reward7, point_reward8;

结果：

campaign_name|account_level|total_point|
-------------|-------------|-----------|
Rwdrdm201049 |            1|      30011|
Rwdrdm201049 |            1|      30018|

问:如何才能使一行的结果变成这样？

campaign_name|account_level|total_point|
-------------|-------------|-----------|
Rwdrdm201049 |            2|      60029|

编辑:这是我的数据看起来像

id                     |campaign_name|point_reward1|point_reward2|point_reward3|point_reward4|point_reward5|point_reward6|point_reward7|point_reward8|campaign_status|is_reported|
-----------------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|---------------|-----------|
RWD00V1o2_0W7x0d5N799nY|Rwdrdm201049 |        10018|        20018|        30018|             |             |             |             |             |NO_RESPONSE    |          0|
RWD7001a2c0Y7r0e557i9Ug|Rwdrdm201049 |        10011|        20011|        30011|             |             |             |             |             |NO_RESPONSE    |          0|

Answer 1

如果您希望每个活动都有一行，那么这应该是GROUP BY中的唯一一列。为了实现这一点，您应该使用apply

select rr.campaign_name, count(DISTINCT rr.id) as account_level,
       v.total_point
from reward_redemption rr cross apply
     (select max(point) as  total_point
      from (values (rr.point_reward1), (rr.point_reward2) , (rr.point_reward3), (rr.point_reward4), (rr.point_reward5) , (rr.point_reward6), (rr.point_reward7), (rr.point_reward8)
           ) v(point)
     ) v
where rr.campaign_status <> 'REJECT_CAMPAIGN' and
      rr.is_reported = 0 and
      rr.campaign_status is not null and
      rr.campaign_name = 'Rwdrdm201049'
group by rr.campaign_name, v.total_point