# SQL Query - Finding Worth of item all database

I've this Table :

``````Trans_ID    Name    Fuzzy_Value    Total_Item
100          I1  0.33333333        3
100          I2  0.33333333        3
100          I5  0.33333333        3
200          I2  0.5               2
200          I5  0.5               2
300          I2  0.5               2
300          I3  0.5               2
400          I1  0.33333333        3
400          I2  0.33333333        3
400          I4  0.33333333        3
500          I1  0.5               2
500          I3  0.5               2
600          I2  0.5               2
600          I3  0.5               2
700          I1  0.5               2
700          I3  0.5               2
800          I1  0.25              4
800          I2  0.25              4
800          I3  0.25              4
800          I5  0.25              4
900          I1  0.33333333        3
900          I2  0.33333333        3
900          I3  0.33333333        3
1000         I1  0.2               5
1000         I2  0.2               5
1000         I4  0.2               5
1000         I6  0.2               5
1000         I8  0.2               5
``````

I have to scan all database for every unique item then count amount of each fuzzy value for your item divided by total transaction (that is 10 within this table)

Example :

``````I1 = Sum of (Fuzzy_Value from item I1 in trans 100 until 1000)
-> (0.33333333+0.33333333+0.5+0.5+0.25+0.33333333+0.2)/10 = 0.244999999
``````

as you can tell I1 appear in transaction `100,400,500,700,800,900,1000`
with worth of `0.33333333+0.33333333+0.5+0.5+0.25+0.33333333+0.2` divide by total transaction, we obtain the amount needed.

``````ITEM_SET           Support
{I1}               0.244999999
{I2}               0.274999999
{I3}               0.258333333
{I4}               0.103333333
``````

How do you do this?

Use:

``````  SELECT t.name AS item_set,
SUM(t.fuzzy_value) / COUNT(*) AS support
FROM TRANS t
GROUP BY t.name
``````

If you want to limit by trans_id:

``````  SELECT t.name AS item_set,
SUM(t.fuzzy_value) / COUNT(*) AS support
FROM TRANS t
WHERE t.trans_id BETWEEN 100 AND 1000
GROUP BY t.name
``````