Need help to make a query

Question

The query below returns two columns - dateOfBusiness, a datetime and salesOnDate, an int. This is the sales generated by one store with an area code storeId for each day. The result set consists of 500-600 rows.

I want to get the average sales for a month, ie do average of salesOnDate. I wanted to use sub query as shown in this SO question. But it did not work for me. How do i do this ?

select count(salesOnDate)
from dbo.localSales
where productName like '%juice%'
and storeId = 'MUN123'
group by dateOfBusiness

I tried to do it like this, but it does not work -

select sum(x.count)
from
(
  select  count(id) as 'count'
  from    table   
) x

Additional info -

Table structure -

dateOfBusiness | salesOnDate
-------------------------------
2013-10-5   | 200
2013-10-6   | 100
2013-10-7   | 700    

I want to sum salesOnDate for any period of time, say one month.

Answer 1

Are you sure you don't want sum(salesOnDate) instead of count(salesOnDate)? Count returns the number of rows, sum will return the total of all the row values. The Average function is AVG, so you could say:

    select 
           dateOfBusiness,storeId , 
           count(salesOnDate), sum(salesOnDate), avg(salesOnDate)
    from 
           dbo.localSales
    where 
          productName like '%juice%'
          and storeId = 'MUN123'
    group by 
          dateOfBusiness,storeId 

OK, given the edits to your question, try:

select 
  storeId , dob_yr, dob_mo, count(salesOnDate), sum(salesOnDate), avg(salesOnDate)
from (
        select 
               dateOfBusiness,storeId , year(dateOfBusiness) as dob_yr, 
               month(dateOfBusiness) as dob_mo,
               salesOnDate
        from 
               dbo.localSales
        where 
              productName like '%juice%'
              and storeId = 'MUN123'
) t
group by
  storeId , dob_yr, dob_mo

Answer 2

This will give you the average sales by month along with the total number of sales for that month:

-- can use DATEPART instead of DATENAME if you prefer numbered months (i.e., 1 = January, 2 = February, etc.)
SELECT DATENAME(month, dateOfBusiness) [Month], SUM(salesOnDate) [TotalSales], AVG(salesOnDate) [AverageSales]
FROM dbo.localSales
WHERE productName like '%juice%'
AND storeId = 'MUN123'
GROUP BY DATENAME(month, dateOfBusiness)

The important part of the query is the DATENAME (or DATEPART if you use that) function, which will only take specific information from your date column, rather than the entire value.

For example, lat's say you have three records for April 11, 2013

   dateOfBusiness     salesOnDate
--------------------- -----------
2013-04-11 08:03:24       5
2013-04-11 11:45:17       1
2013-04-11 20:23:52       3

Using the query above will show them grouped into one month:

     Month          TotalSales      AverageSales
----------------- --------------  ----------------
     April               9               3