SQLTeam.com | Weblogs | Forums

Summing a count over Months


#1

I have situation where I have 3 stores and we want the total count of employees in the in each store for each month; for 4 months. Then would take these totals add them up and devide by the 6 months to get an average.
Exp:
Jan Feb March Apr May
store1 2 3 1 2
Store2 1 2 1 3
Store3 3 1 2 1
-- -- -- --
6 6 4 6 <-- does not have to be shown
4 month average 5.5 This does have to be shown
Cannot figure out the SQL to do this, any ideas would be appreciated.

Thank you


#2
SELECT SUM(Feb+March+Apr+May)*1E/(COUNT(*)*4) FROM Tbl;

Which 4 months? Jan - Apr, or Feb - May? The above assumes Feb-May


#3

Hi,
Thanks for the reply I am not sure that this is it.
in this able example: I am showing the sums for Jan Feb March APR
therefore I have (6 + 6 + 4 + 6 ) Then this would be summed and divided by 4 to get the 5.5 for May.
The idea here though is that at the bottom would be for any month the average for the past 4 months.
Thank you


#4
SELECT SUM(Jan+Feb+March+Apr)/4E FROM Tbl;

If this does not give you what you want, can you post your DDL (i.e., the structure of your table). Does it have one column for each month? If not, how is the data stored?