Sum of duplicated rows

[maggieacast]

How can we calculate the total of a table where there are different granular level of the table?

I have a table like this:

row	date	country	show	campaign	view
1	2022-10-01	US	first_show	abc	10
2	2022-10-01	US	first_show	xyz	10
3	2022-10-01	AU	first_show	def	20
4	2022-10-01	UK	second_show	ghi	10

Row 1 & 2 are the same but since they have different campaign, they are split into different rows. 

I am wondering how can I calculate the total view without the duplicated rows for each show because of the campaign name. 

In SQL runner, I can achieve the calculation by :

WITH temp AS (

   SELECT

      SUM(view) AS view, show

   FROM 

      table

   GROUP BY

      show)

SELECT

   SUM(view) AS total_view

FROM 
   temp

But I am not sure how to achieve this with the LookML code. Ideally it can be done in LookML side so we don’t confuse the user when they are using the explore view.

Any help on this topic is greatly appreciated! 

Thanks!