How to find average in SQL Server?

Question

0.00/5 (No votes)

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 11-Oct-15 20:56pm

ShanifHassan

Updated 11-Oct-15 21:05pm

Magic Wonder

v2

Add a Solution

3 solutions

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
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
) X

Cheers

Posted 11-Oct-15 21:44pm

Magic Wonder

Updated 12-Oct-15 20:18pm

v2

Comments

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tomas Takac · Accepted Answer · 2015-10-12T21:22:00

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

Posted 12-Oct-15 21:22pm

Tomas Takac

Comments

Maciej Los 13-Oct-15 6:16am

5ed!

Maciej Los · Accepted Answer · 2015-10-13T00:28:00

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