Jump to content


Photo

using the SUM function for a SELECT statement?


  • Please log in to reply
4 replies to this topic

#1 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 09 April 2006 - 06:35 PM

I am having trouble figuring out the syntax for this .. or maybe the logic
I have a table called "client_job" and a column called "dollar amount"
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,
column_c (SELECT SUM(dollar_amount) AS gross_income WHERE remittance_date > curdate())

This is one of the variations I tried.

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 


Thanks
n8w

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:59 PM

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

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 09 April 2006 - 07:21 PM

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

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

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

$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


#4 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 09 April 2006 - 07:50 PM

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

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 10 April 2006 - 04:28 PM

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

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users