vendredi 4 décembre 2015

SQL: How to perform this query over each group

I am using SQLite3 and I have the following setup. I have a table which is organised as follows:

ID   k  count
1    a     1 
1    b     2
1    c     5
2    a     3
2    b     4
3    a     2

What I want to do is compute the element wise product-sum between the count column for each ID where they share a k.

So, for example, between 1 and 2, the element wise sum is SUM (1*3 + 2*4) as they share the a and b key. Similarly, between 2 and 3, it will be SUM(3*2) as they only share the 'a' key.

It is because I have to create this for every ID pair i.e. (1, 2), (2, 3), (1, 3), I am not sure how to do this in SQL.

Aucun commentaire:

Enregistrer un commentaire