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

Question

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?

Answer 1

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.