How to sum rows that have the same value in a specific column

Question

I am trying to get a result that displays the revenue generated by a placement. To calculate placement revenue I need to do this calculation (campaign_cpc (in €)*clicks generated by the placement). One placement can generate clicks with different campaign_cpc.

Right now the results that I am getting are like this:

+------------------+---------------------+
| placement_name   | placement_revenue_€ |
+------------------+---------------------+
| placement_name_1 | 147,2               |
+------------------+---------------------+
| placement_name_1 | 170,95              |
+------------------+---------------------+
| placement_name_2 | 14,4                |
+------------------+---------------------+
| placement_name_2 | 51,35               |
+------------------+---------------------+

This is because each placement (Placement 1 & 2) have generated clicks with two different campaign_cpc.

But I actually need the result aggregated by placement:

+------------------+---------------------+
| placement_name   | placement_revenue_€ |
+------------------+---------------------+
| placement_name_1 | 318,15              |
+------------------+---------------------+
| placement_name_2 | 65,75               |
+------------------+---------------------+

The query that I am using is this one:

SELECT
      placement_name,
      campaign_cpc*clicks as placement_revenue_€

FROM (

      SELECT
            p.name as placement_name,
            cc.cpc as campaign_cpc,
            COUNT(CASE WHEN re.event_type = 'click' 
            AND content_card_id IS NOT NULL THEN event_type END) as clicks

      FROM  raw_events re
      JOIN  app_data.placements p ON p.id=re.placement_id
      JOIN  app_data.content_cards cc ON cc.id=re.content_card_id

      GROUP BY 
          cc.cpc,
          p.name
      )

GROUP BY
      placement_name,
      clicks

ORDER BY 
      placement_name

How can I amend the above query to get the aggregated result?

I am using PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1337

Many thanks!

Currently you are grouping by click but there is no field by this name (there is a field clicks). Also, a comma after click will make it not SQL. So something isn't accurate in the question. Basically, @JoshKopen has the correct fix, but perhaps the problem is different. Just use copy-paste, from actual output - it's faster. — Dan Getz
@DanGetz Thank you for your answer. I just corrected the clicks thing. This is because I condensed the query manually before posting, so that it is easier to read (there are many types of click events in the original query). I am still seeing the same results. Any other ideas? Thanks again guys! — Henry
Like @JoshKopen said, remove the clicks from the second (outer) GROUP BY and add a SUM() around the campaign_cpc*clicks and it should do it. But perhaps you are uncondensing the answer and adding something. — Dan Getz
What you guys said is actually correct. Just needed to transfer your comment correctly into the original query. Thank you very much guys, have a great weekend both! :-) — Henry

JoshKopen JoshKopen · Accepted Answer · 2017-06-16T16:56:31

The way to fix your statement to get your desired results is to not group by click as well. Only group by placement_name. This will no longer differentiate rows with different clicks so your summing will work correctly. Also put sum around clicks in your select statement.

How to sum rows that have the same value in a specific column

1 Answers