SQL Query - GROUP BY , PARTITION BY

Question

This is my first post and I am new to SQL

I have a table like

H     Amount    Count      ID 

h1      2         1         x
h2      3         2         x
h3      5         3         x
h1      3         3         x
h1      1         5         y
h2      3         2         x
h3      1         1         x
h3      2         3         y
h2      5         5         y

and I want SUM(Amount*Count) of each H group based on id / Total SUM(Amount*Count) in that H group

i.e

H     value       ID

h1     11/16       x                     value =  (2*1+3*3)/2*1+3*3+1*5 
h1      5/16       y                     value =   1*5/ 2*1+3*3+1*5 
h2      12/37      x 
h2      25/37      y 
h3      16/22      x 
h3       6/22      y

My aim is to group by H and then on EACH GROUP I have to do - Sum(average*count) Over(partition by ID) / Sum(average*count)

but I am not able to write such query can you guys please help me. And sorry about the formatting Thanks

Answer 1

Try this:

SELECT t2.h, t1.value1/t2.value2, t1.id  
FROM 
     (SELECT sum(value) as value1, id  from table
      group by id) as t1, 
     (SELECT sum(value) as value2, h  from table
      group by h) as t2
WHERE t1.h = t2.h

Answer 2

The easy answer is to use an inner query like so:

SELECT SUM(Amount * Count), (SELECT SUM(Amount * Count) FROM table AS t2 WHERE t2.H = t1.H)
FROM table AS t1
GROUP BY H, ID

This essentially refers to the same table as both t1 and t2 for two different queries.

However, the specific database management system you're using (MySQL, Microsoft SQL Server, sqlite, whatever) may have a built-in function to handle this sort of thing. You should look into what your DBMS offers (or tag your question here with a specific platform).

Answer 3

What you want to do is get the dividend value (sum of amount*count per h -> id group) and join the divisor value (sum of amount*count per h group) in another subselect:

SELECT 
    a.h, a.id, a.dividend / b.divisor AS value
FROM
(
    SELECT h, id, SUM(amount*count) AS dividend
    FROM tbl
    GROUP BY h, id
) a 
INNER JOIN
(
    SELECT h, SUM(amount*count) AS divisor
    FROM tbl
    GROUP BY h
) b ON a.h = b.h