Select sum problem 1

[emozley]

Hi,

I am trying to join two tables - One called Users (contains UserID, FirstName, Surname) with another called CPD and I want to get a total of CPDPoints for each user.

So far this works fine:

SELECT Users.UserID, FirstName, Surname, SUM(CPDPoints) AS LowestYearTotal
FROM Users LEFT JOIN CPD ON Users.UserID=CPD.UserID
GROUP BY Users.UserID, Users.FirstName, Users.Surname;

However I want it to only add up CPDPoints for each person where CPD.CPDDate=2004 and store this in LowestYearTotal. In the same recordset I want to have SUM(CPDPoints) AS MiddleYearTotal WHERE CPD.CPDDate=2005.

Am not sure how to go about doing this - any help much appreciated!

Thanks

Ed

 

[lespaul]

what about something like:

SELECT Users.UserID, FirstName, Surname, SUM(A.CPDPoints) AS LowestYearTotal, SUM(B.CPDPoints) AS MiddleYearTotal
FROM Users LEFT JOIN CPD A ON Users.UserID=A.UserID And A.CPDDate = 2004
FROM Users LEFT JOIN CPD B ON Users.UserID=B.UserID And B.CPDDate = 2005
GROUP BY Users.UserID, Users.FirstName, Users.Surname;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[emozley]

Am getting an error message

Syntax error missing operator

 

[emozley]

I have tried changing the query but now it only returns records where CPDDate is 2004 rather than adding them all up.

I have tried searching for 'SQL SELECT SUM' on the web but all the examples only have one total in the recordset. Eventually I will have 6

eg

Ed Mozley | 18 | 6 |13 | 5 | 19 | 6

All the numbers are going to be totals from the same table but I will be using different criteria.

 

[lespaul]

why not something like:

SELECT Users.UserID, FirstName, Surname, CPDDate, SUM(CPDPoints) AS LowestYearTotal
FROM Users LEFT JOIN CPD ON Users.UserID=CPD.UserID
GROUP BY Users.UserID, Users.FirstName, Users.Surname, CPDDate;

this will separate it by the year and then you could use a report to lay out the information in the manner you need.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[emozley]

Sadly this won't work as the lowestyear total will be the total of all records regardless of date.

I think what I am attempting to do may not be possible!

 

[emozley]

Breakthrough! The only problem now is that the recordset columns are showing as Expr1003, Expr1004 and Expr1005 rather than LowesTotal, MiddleTotal and HighestTotal...

SELECT Users.UserID, Users.FirstName, Users.Surname, (SELECT SUM(CPDPoints) As LowestTotal FROM CPD WHERE DatePart('yyyy', CPDDate)=2004 AND Users.UserID=CPD.UserID), (SELECT SUM(CPDPoints) As MiddleTotal FROM CPD WHERE DatePart('yyyy', CPDDate)=2005 AND Users.UserID=CPD.UserID), (SELECT SUM(CPDPoints) As HighestTotal FROM CPD WHERE DatePart('yyyy', CPDDate)=2006 AND Users.UserID=CPD.UserID)
FROM Users LEFT JOIN CPD ON Users.UserID=CPD.UserID
WHERE Users.Visible=TRUE AND Users.Boardroom=FALSE AND ListedDept=FALSE
GROUP BY Users.UserID, Users.FirstName, Users.Surname;

 

[lespaul]

SELECT Users.UserID, Users.FirstName, Users.Surname, (SELECT SUM(CPDPoints) FROM CPD WHERE DatePart('yyyy', CPDDate)=2004 AND Users.UserID=CPD.UserID) As LowestTotal, (SELECT SUM(CPDPoints) FROM CPD WHERE DatePart('yyyy', CPDDate)=2005 AND Users.UserID=CPD.UserID) As MiddleTotal, (SELECT SUM(CPDPoints) FROM CPD WHERE DatePart('yyyy', CPDDate)=2006 AND Users.UserID=CPD.UserID) As HighestTotal
FROM Users LEFT JOIN CPD ON Users.UserID=CPD.UserID
WHERE Users.Visible=TRUE AND Users.Boardroom=FALSE AND ListedDept=FALSE
GROUP BY Users.UserID, Users.FirstName, Users.Surname;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[emozley]

fantastic thanks for your help with this - my final query was as follows:

TBL.Open "SELECT Users.UserID, Users.FirstName, Users.Surname, Departments.Department, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfLowestYear & " AND " & EndOfLowestYear & " AND Users.UserID=CPD.UserID) AS LowestTotal, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfLowestYear & " AND " & EndOfLowestYear & " AND Users.UserID=CPD.UserID AND CPD.Accredited=TRUE) AS LowestAccTotal, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfMiddleYear & " AND " & EndOfMiddleYear & " AND Users.UserID=CPD.UserID) AS MiddleTotal, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfMiddleYear & " AND " & EndOfMiddleYear & " AND Users.UserID=CPD.UserID AND CPD.Accredited=TRUE) AS MiddleAccTotal, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfHighestYear & " AND " & EndOfHighestYear & " AND Users.UserID=CPD.UserID) AS HighestTotal, " &_
" (SELECT SUM(CPDPoints) FROM CPD WHERE Format(CPDDate,'yyyymm') BETWEEN " & StartOfHighestYear & " AND " & EndOfHighestYear & " AND Users.UserID=CPD.UserID AND CPD.Accredited=TRUE) AS HighestAccTotal " &_
" FROM (Users LEFT JOIN CPD ON Users.UserID=CPD.UserID) LEFT JOIN Departments ON Users.Department=Departments.DepartmentID " &_
" WHERE (((Users.Visible)=True) AND ((Users.Boardroom)=False) AND ((Users.ListedDept)=False)) " &_
" AND (FeeEarner=TRUE OR Partner=TRUE) " &_
" GROUP BY Users.UserID, Users.FirstName, Users.Surname, Departments.Department" &_
" ORDER BY " & sort, DB