Double group by clause

[joostschouten]

Hi,

I have a database holding three tables. Projects, timeEntries and expenseEntries. When I select a project from the table, I also want to add a SUM(column) from both the timeEntries and expenseEntries tables. Using a SUM() for only one of the entries table works because I can group on the projectId in on of the tables. Is it possible in some way to group on a different colum for each SUM()?

I know the folowing doesn't work, but it illustrates what I am looking for (note the GROUP BY):

SELECT projects.projectName, SUM(timeEntries.moneySpent), SUM(expenseEntries.moneySpent) FROM projects, timeEntries, expenseEntries WHERE projects.projectId=timeEntries.projectId AND projects.projectId=expenseEntries.projectId GROUP BY timeEntries.projectId AND expenseEntries.projectId

Kind regards,
Joost Schouten

 

[swampBoogie]

SELECT projects.projectName, 
(select SUM(timeEntries.moneySpent) from timeEntries 
WHERE projects.projectId=timeEntries.projectId ) as spendings,
(select sum(expenseEntries.moneySpent) from expenseEntries
WHERE projects.projectId=expenseEntries.projectId) as expenses
FROM projects

Your group by clause is invalid, the syntax is

group by c1,c2