Subquery to get aggregate with different WHERE clause

Question

I'm doing this query where I fetch some data I've persisted from an Adwords Report:

SELECT "client_name", "customer_id",
  sum(clicks) as clicks,
  sum(impressions) as impressions,
  sum(cost) as cost,
  avg(avg_cpc) as avg_cpc,
  avg(conv_rate) as conv_rate,
  sum(total_conv_value) as total_conv_value,
  sum(converted_clicks) as converted_clicks,
  sum(conversions) as conversions,
  avg(search_impr_share) as search_impr_share,
  avg(cost_converted_click) as cost_converted_click,
  avg(average_position) as average_position
FROM "adwords_daily_account_performance"
WHERE "day" >= ?
GROUP BY "customer_id", "client_name"

The table also has a column named network. This column can be either 'Display Network' or 'Search Network'. This query aggregates everything from both networks, but I want to add another column to the query for aggregation called Ctr.

How do I get this the Ctr column where it only aggregates the data where network = 'Search Network' in the same query?

Answer 1

Use a case statement to select the number if the network matches, and a 0 otherwise:

SELECT sum( CASE WHEN network = 'Search Network' THEN Ctr ELSE 0 END )

Answer 2

In Postgres 9.4+ you can use an aggregate FILTER:

sum(Ctr) FILTER (WHERE network = 'Search Network') AS sum_ctr

Details:

• How can I simplify this game statistics query?