using the SUM function for a SELECT statement?

[n8w]

I am having trouble figuring out the syntax for this .. or maybe the logic
I have a table called [b]"client_job"[/b] and a column called [b]"dollar amount"[/b]
I want to get the sum of the column "dollar_amount" with few parameters.

How do I write a statement that says

SELECT
column_a,
column_b,
[b]column_c (SELECT SUM(dollar_amount) AS gross_income WHERE remittance_date > curdate())[/b]

This is one of the variations I tried.

[code]
SELECT
client_job.job_id,
client_job.url_id,
SELECT SUM(dollar_amount) as gross_income FROM client_job WHERE client_job.remittance_date > curdate() GROUP BY job_id,
FROM client_job

[/code]

Thanks
n8w

[fenway]

You need to wrap your subquery in parens and explicity give it a column alias (UNTESTED):

[code]SELECT
client_job.job_id,
client_job.url_id,
(SELECT SUM(dollar_amount) FROM client_job WHERE client_job.remittance_date > curdate() GROUP BY job_id ) as gross_income,
FROM client_job [/code]

[n8w]

Thanks Fenway .. it's not throwing errors now .. but the value is still empty

I have tried putting
[code]
$gross_income=$row[gross_income];[/code]

inside and outside of the while loop with no luck .. any ideas?

[code]$gross_income=$row[gross_income];
while ($row = mysql_fetch_array($retid)) {

                $job_id=$row[job_id];
                $url_id=$row[url_id];
                $client_id=$row[client_id];
                $str_date=$row[str_date];
                $s_job_name=$row[s_job_name];
                $s_body=$row[s_bo[/code]

[n8w]

thanks .. this works great .. just had to chage > to <

[wickning1]

That just doesn't look right to me. It's going to give wrong numbers. Isn't this more what you're looking for?

[code]SELECT j.job_id, j.url_id, g.gross_income
FROM client_job j
LEFT JOIN (
   SELECT job_id, SUM(dollar_amount) as gross_income
   FROM client_job
   WHERE client_job.remittance_date < CURDATE()
   GROUP BY job_id
) g ON g.job_id = j.job_id[/code]