Total a column 1

[ZOR]

I am trying to get a column total for WD1, preferably at the bottom if I can, I have tried the wizard but with no avail.

SELECT Projects.ProjectName, Forecast.WD1
FROM Projects INNER JOIN (Handlers INNER JOIN Forecast ON Handlers.HandlerID = Forecast.HandlerID) ON Projects.ProjID = Handlers.ProjID
GROUP BY Projects.ProjectName, Forecast.WD1;

Many thanks

 

[Golom]

Try

SELECT Projects.ProjectName, [COLOR=red]SUM([/color]Forecast.WD1[COLOR=red]) As SumWD1[/color]

FROM Projects INNER JOIN (Handlers 
     INNER JOIN Forecast ON Handlers.HandlerID = Forecast.HandlerID) 
     ON Projects.ProjID = Handlers.ProjID

GROUP BY Projects.ProjectName;

 

[ZOR]

Many thanks Golam. However it did not give me a total of WD1 for all records, just gave a column of individual row totals. My aim is to try and get a foot/column total. Thanks the same.

 

[Golom]

Since your submitted query showed ProjectName, I assumed that you wanted totals by ProjectName. If you just want one grand total then

SELECT SUM(Forecast.WD1) As SumWD1

FROM Projects INNER JOIN (Handlers 
     INNER JOIN Forecast ON Handlers.HandlerID = Forecast.HandlerID) 
     ON Projects.ProjID = Handlers.ProjID

 

[ZOR]

Sorry Golam, maybe I have confused the issue?? I am trying to get this:

Project Name | WD1 |
Proj 1 | 5 |
Proj 2 | 6 |
Total | 11 |

Does that help. Thanks again

 

[Golom]

OK. Combine the two

SELECT Projects.ProjectName, SUM(Forecast.WD1) As SumWD1

FROM Projects INNER JOIN (Handlers 
     INNER JOIN Forecast ON Handlers.HandlerID = Forecast.HandlerID) 
     ON Projects.ProjID = Handlers.ProjID

GROUP BY Projects.ProjectName

UNION ALL

SELECT "Total", SUM(Forecast.WD1) As SumWD1

FROM Projects INNER JOIN (Handlers 
     INNER JOIN Forecast ON Handlers.HandlerID = Forecast.HandlerID) 
     ON Projects.ProjID = Handlers.ProjID

 

[ZOR]

Excellent Golam, thanks a lot. Just what I wanted. Have a star and thanks again.