SUM of distinct and unique column values

Question

site      device      site_count    
=================================
1           AB1          45            
1           AB2          45            
1           AB3          45            
1           AB4          45            
1           AB5          45            
2           AB6          70            
2           AB7          70            
2           AB8          70            
3           AB9          100           
3           A10          100           
4           A11          10   

The below sum(site_count) is obtained by adding site count value of unique sites i.e 45+70+100+10 =225 (irrespective of no of times the site appeared).

site      device      site_count     sum(site_count)
====================================================
1           AB1          45            225
1           AB2          45            225
1           AB3          45            225
1           AB4          45            225
1           AB5          45            225
2           AB6          70            225
2           AB7          70            225
2           AB8          70            225
3           AB9          100           225
3           A10          100           225
4           A11          10            225

Can you post a query to get the sum (site_count) and other columns values in one single query. The result set is displayed above.

Answer 1

You can use distinct on sum:

select site, device, site_count, sum(distinct site_count) over ()
from mytable;

EDIT: Of course you can do the same without a windowing function, but this may be slower:

select site, device, site_count, (select sum(distinct site_count) from mytable)
from mytable;

NEW EDIT: I misread your request. In your comment below you've explained that you are not looking for distinct site counts, but merely for the sum of site counts over all sites. So here is an update to my answer.

Unfortunately you don't have one table for sites and one table for devices, as you should, but only a table for devices holding the site count redundantly. You should change that. Having said this, of course even with a disadvantageous data model like yours, it is still possible to select the data. It's only a little more work.

You need the site count once per site. So group by site and take the min, max or avg site count - they should all be the same as the value is stored redundantly. Then sum those site counts:

select site, device, site_count, 
(
  select sum(distinct_site_count) 
  from
  (
    select min(site_count) as distinct_site_count from mytable group by site
  )
)
from mytable;

Or more compact:

select site, device, site_count, 
  (select sum(min(site_count)) from mytable group by site)
from mytable;