SUM of SUM in BigQuery without duplicates
<p>I have a table with a one-to-many relationship of a business' name to a ZIP code, due to multiple industry codes matching that business for a given ZIP code. A separate table contains households by ZIP codes. To sum the households by business name for a range of ZIP codes without duplicates, I query:</p>

<pre><code>SELECT DBAName, SUM(Households) OVER (PARTITION BY ZIPCode) AS SumHouseholds  
FROM Business  
JOIN Location  
ON Location.ZIPCode = Business.ZIPCode  
WHERE ZIPCode like '1001%' and DBAName = 'ABC Plumbing'  
GROUP BY DBAName, ZIPCode, Households  
ORDER BY SumHouseholds Desc  
</code></pre>

<p>And get:  </p>

<pre><code>Row DBAName SumHouseholds  
1   ABC Plumbing    30  
2   ABC Plumbing    25  
3   ABC Plumbing    5  
</code></pre>

<p>Each row represents a distinct ZIP code within the range, and its deduplicated sum of households. However, I'd like to sum the SumHouseholds from those rows = 60. When I try to nest SELECT statements for this:</p>

<pre><code>SELECT DBAName, SUM(SumHouseholds) AS TotalHouseholds FROM (  
SELECT DBAName, SUM(Households) OVER (PARTITION BY ZIPCode) AS SumHouseholds  
FROM Business  
JOIN Location  
ON Location.ZIPCode = Business.ZIPCode  
WHERE ZIPCode like '1001%' and DBAName = 'ABC Plumbing')  
GROUP BY DBAName  
ORDER BY TotalHouseholds  
</code></pre>

<p>It returns the the sum of Households for all matching records <em>with</em> duplicates. Since 3 records match the query for each ZIP code, the result is <code>30x3+25x3+5x3=180</code>.</p>

<p>How should I change the query to return <code>SUM(SumHouseholds)=60</code>?</p>