How can I do a simple SUM query

Question

How can I do a simple SUM query

stephen.aa

SSC Eights!

Points: 815
More actions
November 20, 2019 at 5:45 pm

#3699285

This is not working. It does not like the Date used, and Something about aggregate and group by clauses...
SELECT EmpNo, Date1, SUM(Time) as Expr1
FROM TimeData
WHERE (EmpNo = '8939') AND (Date1 = #11/18/2019#)
GROUP BY EmpNo
Steve Anderson

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

When you use a GROUP BY everything in the SELECT clause has to be a property of the group. You can achieve this by using the expression to define the group (EmpNo in this case) or by using an aggregate SUM(Time). Date1 is a property of individual records, not the group as a whole, because it is neither used to define the group nor is it used in an aggregate. Since you only have one possible date based on your criteria, you probably want to add it to the GROUP BY clause.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

On top of everything Drew posted, you need to change the "#' signs to single quotes and, depending on the datatype for the Time column, you might not be able to take a sum of the time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 3

I believe that the # are from MS Access SQL. That's why I didn't comment on them.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Ah... thanks, Drew.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stephen.aa SSC Eights! Points: 815 More actions · Answer 5

Thank you.l This is what I ended up with!

SELECT EmpNo, Date1, SUM(Time) AS TotalTime

FROM TimeData

WHERE (EmpNo = @EmpNo) AND (Date1 = @Date)

GROUP BY Date1, EmpNo

Steve Anderson