15,997,667 members
See more:
Hello All,

I have to find the average of total visitors in my shop day wise. For eg, if I'm generating query for last 30 days, the report should be like
SQL
```Date      Customers         Average

1 Oct           100          100
2 Oct           50          (100+50)/2
3 Oct          100          (100+50+100)/3
4 Oct          100           (100+50+100+100)/4 etc```

Can anyone help me to write sql query for this? Please find the query to find customer count. I want to add average column in it.
SQL
```select branch.br_name ,tran_date as entry_date ,count(distinct slip_no) as no_of_cust
from rem_upload , branch
where rem_upload.location_id = branch.br_code
and ( ( rem_upload.location_id like '002' )
AND  ( rem_upload.tran_date ='01/01/2015' )
AND  ( rem_upload.tran_date = '12/10/2015' ) )
group by branch.br_name  ,tran_date```
Posted
Updated 11-Oct-15 21:05pm
v2

## Solution 2

If you have SQL server 2012 or higher you can use windowing functions:
SQL
`select [date], [customers], AVG([customers]) OVER(ORDER BY [date]) as [average] from t`

Maciej Los 13-Oct-15 6:16am
5ed!

## Solution 3

In addition to solution 2 by Tomas Takac[^], if you use SQL Server down to 2012, you can achieve the same using this:
SQL
```DECLARE @tmp TABLE(VisitDate DATE, CustomersCount INT)

INSERT INTO @tmp (VisitDate, CustomersCount)
VALUES('2015-10-01', 100),
('2015-10-02', 50),
('2015-10-03', 100),
('2015-10-04', 100)

SELECT t1.VisitDate, SUM(t2.CustomersCount) AS RunningSum, COUNT(t2.VisitDate) AS CountOfDays, SUM(t2.CustomersCount)/COUNT(t2.VisitDate) AS [Average]
FROM @tmp AS t1 INNER JOIN @tmp AS t2 ON t1.VisitDate>=t2.VisitDate
GROUP BY t1.VisitDate```

```VisitDate	RunningSum	CountOfDays	Average
2015-10-01	100			1			100
2015-10-02	150			2			75
2015-10-03	250			3			83
2015-10-04	350			4			87```

v2
Tomas Takac 13-Oct-15 8:53am
+5, my solution isn't really complete without this.
Maciej Los 13-Oct-15 10:44am
Thank you, Tomas.

## Solution 1

Hi,

Check below example, hope this will help you...

SQL
```--My Table based on your requirement
Day1                    | count1
------------------------------------------
2015-10-01 12:38:19.487 | 100
2015-10-02 12:38:26.253 | 50
2015-10-03 12:38:33.540 | 150
2015-10-04 12:38:43.530 | 100

--Query to fetch Total, Avg Counts

SELECT DAY(day1) as DayCount,
count1 as NoOfCustomer,
Count1 + ISNULL((SELECt SUM(count1) from Stats_Test B where DAY(B.Day1) < DAY(A.Day1)),0) as TotalNoOfCustomer,
(Count1 + ISNULL((SELECt SuM(count1) from Stats_Test B where DAY(B.Day1) < DAY(A.Day1) ),0))/DAY(day1) as AvGNoOfCustomer
from
Stats_Test A

--And OutPut is

DayCount	| NoOfCustomer	| TotalNoOfCustomer	| AvGNoOfCustomer
--------------------------------------------------------------------------
1	        | 100	        | 100	                | 100
2	        | 50	        | 150	                | 75
3	        | 150	        | 300	                | 100
4	        | 100	        | 400	                | 100```

You can customize above query to fit in your requirement.

Check Your Query...hope it should work as i have not run this.

SQL
```Select X.br_name,
X.entry_date,
X.no_of_cust,
(X.no_of_cust+ ISNULL((SELECt SuM(X.no_of_cust) from X B where DAY(B.entry_date) < DAY(X.entry_date) ),0))/DAY(X.entry_date) as AvgCount
(
select branch.br_name,
tran_date as entry_date,
count(distinct slip_no) as no_of_cust
where rem_upload.location_id = branch.br_code
and ( ( rem_upload.location_id like '002' )
AND  ( rem_upload.tran_date ='01/01/2015' )
AND  ( rem_upload.tran_date = '12/10/2015' ) )
group by branch.br_name,tran_date
) X```

Cheers

v2
ShanifHassan 12-Oct-15 9:23am
thanks for the reply. But a small issue. Since I'm fetching count1 using count function, when I use SUM(count1), an error is generating "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
Magic Wonder 13-Oct-15 2:13am
So you mean to say, you are fetching customer count using aggregate function?
Magic Wonder 13-Oct-15 2:19am
Check Updated Solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard Deeming 40 George Swan 30 Manas Bhardwaj 5 Noreen Gonz Mar -16
 OriginalGriff 431 Pete O'Hanlon 225 Richard Deeming 165 Dave Kreskowiak 123 CPallini 110

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900