使用SQL在不同的值上使用group by设置状态值

I have this SQL query

SELECT SUM(reach) AS reach, SUM(impressions) AS impressions, cpc, id_name,
       SUM(clicks) AS clicks, SUM(amount_spent) AS amount, pagename, status
FROM mbk_ad_data
WHERE id_campaign_shortname = 'name'
  AND adset_name NOT LIKE '%MSN%'
  AND date_from = '2016-02-02'
  AND date_to = '2016-02-09'
GROUP BY id_name

That will group and output this:

reach impressions cpc      id_name clicks amount pagename status
4099  4529        6.34875  name    29    246.11  Name     paused

This works almost as intended. I have two different rows with "pagename" Name and the summed up values are correct, but row 1 has status "active" and row 2 has status "paused". What I want is to have a status "active" in the output, if one of the rows has a status "active", so my output will be:

reach impressions cpc      id_name clicks amount pagename status
4099  4529        6.34875  name    29    246.11  Name     active

How can do this in the query?

Apply MIN on status:

SELECT SUM(reach) AS reach, SUM(impressions) AS impressions, cpc, id_name,
       SUM(clicks) AS clicks, SUM(amount_spent) AS amount, pagename, 
       MIN(status) AS status
FROM mbk_ad_data
WHERE id_campaign_shortname = 'name'
      AND adset_name NOT LIKE '%MSN%'
      AND date_from = '2016-02-02'
      AND date_to = '2016-02-09'
GROUP BY id_name

This way status = 'active' will take precedence over status = 'paused'. This will work as long as the group contains just these two values for status field.

You should get into the habit of including all non-aggregated columns in the GROUP BY.

Then, the rest can be done using aggregation:

SELECT SUM(reach) AS reach, SUM(impressions) AS impressions, cpc, id_name, 
       SUM(clicks) AS clicks, SUM(amount_spent) AS amount, pagename,
       (CASE WHEN MAX(status = 'active') > 0 THEN 'active'
             ELSE MAX(status)
        END) as status
FROM mbk_ad_data
WHERE id_campaign_shortname = 'name' AND adset_name NOT LIKE '%MSN%' AND
      date_from = '2016-02-02' AND date_to = '2016-02-09'
GROUP BY id_name, cpc, id_name, pagename;

Although there might be a very slight performance hit for including all the columns, including all the columns more than makes up for that problem by (1) being standard SQL and (2) by preventing unintentional errors.