CODEWITHSUNDEEP
sqlstaticaverage
gulbaek
gulbaek

Reputation: 2541

Sql: Calc average times a customers ordered a product in a period

How would you calc how many times a product is sold in average in a week or month, year.

I'm not interested in the Amount, but how many times a customer has bought a given product.

OrderLine

 OrderNo | ProductNo | Amount |
----------------------------------------
       1 |         1 |     10 |
       1 |         4 |      2 |
       2 |         1 |      2 |
       3 |         1 |      4 |

Order

     OrderNo | OrderDate
  ----------------------------------------
           1 | 2012-02-21
           2 | 2012-02-22
           3 | 2012-02-25

This is the output I'm looking for

ProductNo | Average Orders a Week | Average Orders a month |
------------------------------------------------------------
        1 |                     3 |                     12 |
        2 |                     5 |                     20 |

Upvotes: 0

Views: 410

Answers (2)

Bridge
Bridge

Reputation: 30691

This is a copy of DRapp's answer, but coded for SQL Server (it's too big for a comment!)

SELECT PreCount.ProductNo,
       PreCount.TotalCount / PreCount.CountOfYrWeeks AS AvgPerWeek,
       PreCount.TotalCount / PreCount.CountOfYrMonths AS AvgPerMonth,
       PreCount.TotalCount / PreCount.CountOfYears AS AvgPerYear
FROM   (SELECT OL.ProductNo,
               Count(*) TotalCount,
               Count(DISTINCT Datepart(wk, O.OrderDate)) AS CountOfYrWeeks,
               Count(DISTINCT Datepart(mm, O.OrderDate)) AS CountOfYrMonths,
               Count(DISTINCT Year(O.OrderDate)) AS CountOfYears
        FROM   OrderLine OL  JOIN [Order] O
                 ON OL.OrderNo = O.OrderNo
        GROUP  BY OL.ProductNo) PreCount

Upvotes: 1

DRapp
DRapp

Reputation: 48159

You would have to first pre-query it grouped and counted per averaging method you wanted. To distinguish between year 1 and 2, I would add year() of the transaction into the grouping qualifier for distinctness. Such as Sales in Jan 2010 vs Sales in 2011 vs 2012... similarly, week 1 of 2010, week 1 of 2011 and 2012 instead of counting as all 3 years as a single week.

The following could be done if you are using MySQL

select
      PreCount.ProductNo,
      PreCount.TotalCount / PreCount.CountOfYrWeeks as AvgPerWeek,
      PreCount.TotalCount / PreCount.CountOfYrMonths as AvgPerMonth,
      PreCount.TotalCount / PreCount.CountOfYears as AvgPerYear
   from
      ( select
              OL.ProductNo,
              count(*) TotalCount,
              count( distinct YEARWEEK( O.OrderDate ) ) as CountOfYrWeeks,
              count( distinct Date_Format( O.OrderDate, "%Y%M" )) as CountOfYrMonths,
              count( distinct Year( O.OrderDate )) as CountOfYears
           from
              OrderLine OL
                 JOIN Order O
                    on OL.OrderNo = O.OrderNo
           group by
              OL.ProductNo ) PreCount

Upvotes: 3

Related Questions