SQL - return all rows, however non-unique rows are merged into one (arithmetic performed)?

I have a table where columns B, C, a开发者_如何学JAVAnd D can be considered identifiers, and columns E - I are values. There will be some rows which are non-unique according to the identifiers i.e. the values for B, C and D match with those of some other row (possibly more than one row match).

I would like a query which returns all the rows as they are, with the exception of those non-unique rows. In these cases I would like to have only one row returned, with their values summed, with one exception, column E. This should be a weighted average with respect to column F i.e for every matching row the value returned should be the sum of E*F / sum of F

e.g

E, F  
7, 1  
9, 4  
E = (7*1 + 9*4)/(1+4) = 8.6

What query can I use to achieve to return the desired set of results?

All help is much appreciated!

---

I think this should do it:

select B, C, D, sum(E*F) / sum(F), sum(F), sum(G), sum(H), sum(I)
from MyTable
group by B, C, D

---

SELECT B, C, D,
       SUM(E * F) / SUM(F) AS E_WEIGHTED,
       SUM(F) AS F_SUM,
       SUM(G) AS G_SUM,
       SUM(H) AS H_SUM,
       SUM(I) AS I_SUM
  FROM TableX
GROUP BY B, C, D

In general the GROUP BY clause is what will consolidate rows that share duplicate values (those columns you name in the GROUP BY clause).

---

SELECT B,C,D,SUM(E*F)/SUM(F), SUM(F), SUM(G), SUM(H), SUM(I)
FROM my_table
GROUP BY B,C,D;