sum of row value date wise

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

create table Table1
(
TB1_ID varchar(100),
TB1_DT varchar(100),
TB1_PRC varchar(100),
TB1_US varchar(100)
)

select * from Table1

and

my table is:
TB1_ID  TB1_DT        TB1_PRC   TB1_US

1	2/8/2013	100	60
2	5/8/2013	1000	600
3	7/8/2013	11000	6100
4	8/8/2013	12000	6200
5	9/8/2013	13000	6300

i want to get average(for many values) of TB1_PRC from TB1_DT 5/8/2013 to 10/8/2013.
but how.........

Posted 11-Aug-13 21:51pm

arvindnitin7

Add a Solution

Comments

OriginalGriff 12-Aug-13 4:37am

Answer updated

2 solutions

Solution 2

OriginallGriff's answer is very good, but i would suggest to create table with proper data types:

SQL

create table Table1
(
TB1_ID INT IDENTITY(1,1),
TB1_DT DATE --or DATETIME or SMALLDATETIME,
TB1_PRC INT,
TB1_US INT
)

For furher information, please see: T-SQL Data Types[^]

Posted 11-Aug-13 22:31pm

Maciej Los

Comments

OriginalGriff 12-Aug-13 4:37am

:thumbsup:

Maciej Los 12-Aug-13 4:41am

;)

ridoy 12-Aug-13 4:40am

+5

Maciej Los 12-Aug-13 4:41am

Thank you ;)

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)

OriginalGriff · Accepted Answer · 2013-08-11T22:15:00

Try:

SQL

SELECT AVG(TB1_PRC) FROM Table1 WHERE TB1_DT BETWEEN '2013-08-05' AND '2013-08-10'

"Maciej Los has posted a comment to the Answer "sum of row value date wise":
Paul, please have a look at the structure of database... All fields are varchar data types. A bit of comment on that?
"

Maciej is absolutely right - it won't work as given if you store your dates in text form: so don't. Always store data in the most appropriate format: for dates that is DateTime or DateTime2, becaue keeping them in any other format is going to give you headaches. Text is a particularly poors choice for two main reasons:
1) The sort order is wrong:

2) Text dates imply that they are entered as text in some form from a different system or systems - which may not all share the same locale and thus have different date formats: how are you going to cope with

1/12/2013
12/1/2013
2013-12-1
13/12/1
12-1-13
1st Dec '13

Storing dates in a date field solves these problems provided they are passed to SQL as DateTime values rather than strings - as they should be anyway to prevent SQL Injection attacks.

Change your database, then use the code above.