MySQL sum of sub queries

I have quite a long query that is causing me some problems. For the first sub-query I keep getting the error: "MySQL server version for the right syntax to use near 'SELECT project.project_total_num_hours_quoted FROM project inner join time_recor' at line 5".

The subquery in question is:

sum(SELECT
    project.project_total_num_hours_quoted
    FROM
    project inner join time_recording using(project_id)
    WHERE
    project.company_id = company.company_id
    AND project_is_retainer != 1
    AND time_recording.time_recording_event_start_datetime >= '2011-01-01' AND time_recording.time_recording_event_stop_datetime <= '2011-03-01'
    group by project_id
    ) AS hours_quoted,

This returns a set of results. In the larger query I simply want to have the sum.

SELECT 
SUM((unix_timestamp(time_recording.time_recording_event_stop_datetime)-unix_timestamp(time_recording.time_recording_event_start_datetime))/3600) AS total_time, 
company.company_label,

sum(SELECT
project.project_total_num_hours_quoted
FROM
project inner join time_recording using(project_id)
WHERE
project.company_id = company.company_id
AND project_is_retainer != 1
AND time_recording.time_recording_event_start_datetime >= '2011-01-01' AND time_recording.time_recording_event_stop_datetime <= '2011-03-01'
group by project_id
) AS hours_quoted,

(SELECT SUM(project.project_total_num_hours_quoted)
FROM project 
INNER JOIN time_r开发者_C百科ecording ON project.project_id = time_recording.project_id
WHERE time_recording.time_recording_event_start_datetime>='2011-01-01'
AND project_is_retainer!=1
AND time_recording.time_recording_event_stop_datetime<='2011-03-01'
AND project.company_id!=1
) AS total_hours_quoted,

(
SELECT 
SUM((unix_timestamp(time_recording.time_recording_event_stop_datetime)-unix_timestamp(time_recording.time_recording_event_start_datetime))/3600) 
FROM time_recording 
INNER JOIN project ON time_recording.project_id = project.project_id 
WHERE project.company_id!=1 
AND project_is_retainer!=1
AND time_recording.time_recording_event_start_datetime>='2011-01-01'
AND time_recording.time_recording_event_stop_datetime<='2011-03-01'
)
AS total_hours

FROM time_recording 
INNER JOIN project ON time_recording.project_id = project.project_id
INNER JOIN company ON project.company_id = company.company_id
WHERE company.company_id!=1
AND project_is_retainer!=1
AND time_recording.time_recording_event_start_datetime>='2011-01-01'
AND time_recording.time_recording_event_stop_datetime<='2011-03-01'
GROUP BY company.company_id 
ORDER BY total_time desc 
LIMIT 7

In your first subquery, you don't need the group by if you sum it in the outer query. And you are missing the ON clause.

SELECT project.project_total_num_hours_quoted
FROM project inner join time_recording 
ON project.id=time_recording.project_id
WHERE
project.company_id = company.company_id
AND project_is_retainer != 1
AND time_recording.time_recording_event_start_datetime >= '2011-01-01' 
AND time_recording.time_recording_event_stop_datetime <= '2011-03-01'

I would strongly recommend scrapping this and starting again.

Several, if not all, the subselects could be merged into a single SELECT statement. The outer SELECT is an aggregate operation which selects non-aggregated values not included in the GROUP BY clause. MySQL does not optimize push-predicates. And you've got redundant joins in the query.