sql SUM of a value error

Hi,
Im trying to get the total of a field in a database using the sql - SUM(Timespent) as Totaltimespent, Timespent is the name of the column with the numbers i want to add up.
My code below doesnt work
I'm using MS SQL
Thanks
SELECT tblClient.[name] as ClientName  ,  tblProject.[Name] as ProjectName  , tblEmployee.[Name] as EmployeeName , Date ,Timespent ,SUM(Timespent) as Totaltimespent, Task ,EmployeeID,tblTimesheet.ID as ID FROM tblTimesheet INNER JOIN tblProject on tblTimesheet.ProjectID = tblProject.ID INNER JOIN tblEmployee  on tblTimesheet.EmployeeID = tblEmployee.ID INNER JOIN tblClient on tblProject.ClientID = tblClient.ID
WHERE ProjectID=@ProjectID AND tblProject.ClientID =@ClientID

Open in new window

CharlieDevAsked:
Who is Participating?
 
MilleniumaireCommented:
Try the following:
SELECT tblClient.[name] as ClientName,
       tblProject.[Name] as ProjectName,
       tblEmployee.[Name] as EmployeeName,
       tblTimesheet.ID as ID,
       SUM(Timespent) as Totaltimespent
FROM tblTimesheet 
     INNER JOIN tblProject 
        on tblTimesheet.ProjectID = tblProject.ID 
     INNER JOIN tblEmployee  
        on tblTimesheet.EmployeeID = tblEmployee.ID 
     INNER JOIN tblClient 
        on tblProject.ClientID = tblClient.ID
WHERE ProjectID=@ProjectID 
AND   tblProject.ClientID =@ClientID
GROUP BY tblClient.[name],
         tblProject.[Name],
         tblEmployee.[Name],
         tblTimesheet.ID 

Open in new window

0
 
mirzasCommented:
You need to use a GROUP BY
0
 
mirzasCommented:
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CharlieDevAuthor Commented:
Where do I put that?
Thanks
0
 
quincydudeCommented:
mirzas:, we do not necessarily use group by for getting sum
CharlieDev, please provide more detail like table structure and what you are querying.
0
 
MilleniumaireCommented:
You need to use a group by statement if you are retrieving columns that are not aggregated.
0
 
mirzasCommented:
quincydude, it is one of the options available for the given criteria - it gives context to he aggregate functions.
0
 
CharlieDevAuthor Commented:
thanks for all the advice i have been advised that this method is not going to work for what i want so i'm now using asp.net code to add up the totals
Thanks
0
 
CharlieDevAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.