1

Suppose I have a table that contains a number of transactions but I'd like to weight them by the number of stores in that state, is there a SQL command I can use that calculates the weighted average and not just the average (AVG())?

|STATE  | NUMBER OF STORES | NUMBER OF TRANSACTIONS   | 
|-------|------------------|--------------------------|   
| ca    |   25             |        500               |
|-------|------------------|--------------------------| 
| il    |   2              |        25                |
|-------|------------------|--------------------------|
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Ally
  • 35
  • 1
  • 7
  • http://stackoverflow.com/questions/1698408/weighted-average-in-t-sql-like-excels-sumproduct – James Gaunt Dec 07 '16 at 18:23
  • What is your number of transactions column? Is it a total for all stores in the state? What is the desired result for your example data? – Martin Smith Dec 07 '16 at 18:32
  • yes the transactions column is a total of all orders/sales for stores in that state. in the example i provided, among the 25 stores in california, there have been 500 sales. – Ally Dec 07 '16 at 18:33
  • Possible duplicate of [Weighted average in T-SQL (like Excel's SUMPRODUCT)](https://stackoverflow.com/questions/1698408/weighted-average-in-t-sql-like-excels-sumproduct) –  Sep 15 '17 at 02:53
  • Please read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) before attempting to ask more questions. –  Sep 15 '17 at 02:53

1 Answers1

4

No.

Just use 

Sum(1.0 * [NUMBER OF TRANSACTIONS])/SUM([Number Of Stores])

Which gives 19.44 for your example data.

California has an average of 20 and Illinois 12.5 but California has 25 stores vs 2 so is weighted heavier.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845