How to calculate the sum per day

Question

0.00/5 (No votes)

See more:

HI

I am currently creating a report for my client and would like to know how would i be able to calculate the amount of records processed by a user for each day.

My table is Stage1

id(int),product(Nvarchar),date(datetime),userid(int),stage(int)

what would my query be to select the amount of products entered by a user?

i need to get the count of products passed per day by a user

Select Sum(userid),Workcompleted from Table group by workcompleted

Posted 13-Feb-13 19:35pm

isi19

Updated 13-Feb-13 20:29pm

v2

Add a Solution

Comments

Aarti Meswania 14-Feb-13 1:49am

not clear question
what is your table and fields ?

4 solutions

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)

_Maxxx_ · Answer 1 · 2013-02-13T19:50:00

Solution 1

SQL

Select Sum(Workcompleted ) from Table where userid = @userid

would do it, assuming workcompleted is the number of records processed

if you just want to counmt the records then

SQL

select count(*) from Table where userId = @userId

Posted 13-Feb-13 19:50pm

_Maxxx_

Bhushan Shah1988 · Answer 2 · 2013-02-13T19:51:00

Solution 2

try below T-SQL code :

SQL

CREATE TABLE #TEMP
(
	ITEM	VARCHAR(50),
	AMOUNT	INT,
	INDATE  DATETIME,
	USERID	INT
)

INSERT INTO #TEMP VALUES('WATCH',500,'02/13/2013',1)
INSERT INTO #TEMP VALUES('BELT',300,'02/13/2013',1)
INSERT INTO #TEMP VALUES('WALLET',600,'02/13/2013',2)
INSERT INTO #TEMP VALUES('TROUSERS',1600,'02/13/2013',2)

SELECT USERID,INDATE,SUM(AMOUNT) AS TOTALAMOUNT
FROM #TEMP
GROUP BY USERID,INDATE

DROP TABLE #TEMP

Posted 13-Feb-13 19:51pm

Bhushan Shah1988

Comments

isi19 14-Feb-13 2:06am

My table is

id,product,date,userid,stage

what would my query be to select the amount of products entered by a user?

isi19 14-Feb-13 2:06am

i need to get the count of products passed per day per user

Bhushan Shah1988 14-Feb-13 7:44am

try this :

CREATE TABLE #TEMP
(
ITEM VARCHAR(50),
AMOUNT INT,
INDATE DATETIME,
USERID INT
)

INSERT INTO #TEMP VALUES('WATCH',500,'02/13/2013',1)
INSERT INTO #TEMP VALUES('WATCH',500,'02/13/2013',1)
INSERT INTO #TEMP VALUES('BELT',300,'02/13/2013',1)
INSERT INTO #TEMP VALUES('WALLET',600,'02/13/2013',2)
INSERT INTO #TEMP VALUES('WALLET',700,'02/13/2013',2)
INSERT INTO #TEMP VALUES('TROUSERS',1600,'02/13/2013',2)
INSERT INTO #TEMP VALUES('TROUSERS',1300,'02/13/2013',2)

SELECT USERID,COUNT(*) AS NoofProducts
FROM #TEMP
GROUP BY USERID

DROP TABLE #TEMP

Avik Ghosh22 · Answer 3 · 2013-02-13T19:51:00

Solution 3

select sum(amt),id from tablex where date='2012-10-10' and id= 1 group by id

Posted 13-Feb-13 19:51pm

Avik Ghosh22

Updated 13-Feb-13 19:55pm

v2

Maciej Los · Answer 4 · 2013-02-13T21:22:00

The best way to achieve that is to use Stored procedure[^], like this:

SQL

CREATE PROCEDURE GetAmountPerUserAndDate
    @dateFrom AS DATE,
    @dateTo AS DATE

AS 

BEGIN

    SELECT [userid], [date], SUM(Amount)
    FROM Stage1
    WHERE [date] BETWEEN COALESCE(@dateFrom,GETDATE()) AND COALESCE(@DateTo,GETDATE())
    GROUP BY [userid], [date]
    ORDER By [userid], [date]

END