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?

score 3 · Accepted Answer · edited Nov 28 '14 at 14:11

3

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 )

edited Nov 28 '14 at 14:11

Gordon Linoff

1,242,037
58
646
786

answered Nov 28 '14 at 11:23

Erik

3,598
14
29

Thank you! It didn't work initially. I think a WHEN clause was missing cause when I wrote: `avg(CASE WHEN network=\'Search Network\' THEN Ctr ELSE 0 END ) as Ctr` It worked, but I have a problem. It seems that the average is wrong cause it adds 0 every time it isnt the correct network. – dadord Nov 28 '14 at 11:41
1

Oh... I'm not sure how to do this with an AVG actually. You could try doing "ELSE NULL", it might skip those in calculating the average. But it could also interpret them as 0, in which case it might not be possible to do in a single query. – Erik Nov 28 '14 at 12:29
@dadord . . . Just drop the `else` clause. The `case` will then return `NULL` which is ignored by most of the aggregation functions. (`NULL` is *not* treated as `0`.) – Gordon Linoff Nov 28 '14 at 14:11

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

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?

edited May 23 '17 at 11:43

Community

1
1

answered Nov 28 '14 at 17:18

Erwin Brandstetter

605,456
145
1,078
1,228

Subquery to get aggregate with different WHERE clause

2 Answers2