SQL Query - Finding Value of item from all database

I have 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 need to scan all database for each unique item then count sum of each fuzzy value for that item divided by total transaction (which is 10 in 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 see I1 exist in transaction 100,400,500,700,800,900,1000 ; with value of 0.33333333+0.33333333+0.5+0.5+0.25+0.33333333+0.2 divide by total transaction, we get the number needed.

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

How do i do that?

Answers


Use:

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

If you need 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

Need Your Help

Mysql date between two dates in average interval per seconds

php mysql

I have one table which have number of records entered on exact time in field add_date.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.