Is this query possible??

[Mincer]

Ok, from this basic query that select ALL unbilled items from the unbilled_item table along with the respective project manager for each job from the job table, is it possible to sum the unbilled_item.amount's associated with each unbilled_item.job_number and then order by this total.

So, I am trying to list jobs by total unbilled amount.

Many thanks.

	$sql = "
	SELECT unbilled_item.job_number,unbilled_item.week_ending,unbilled_item.amount,job.project_manager
	FROM unbilled_item 
	LEFT JOIN job ON (unbilled_item.job_number=job.job_number)
	" ;

[freakysid]

Hi, I am not sure why you are using a LEFT JOIN rather than an [inner] JOIN on the two tables. Is it that an unbilled item might not be related to a job?

Anyway, you want to use the SUM function in conjuction with a GROUP BY clause:

SELECT unbilled_item.job_number,
       unbilled_item.week_ending,
       SUM(unbilled_item.amount),
       job.project_manager
FROM unbilled_item 
LEFT JOIN job ON (unbilled_item.job_number=job.job_number)
GROUP BY unbilled_item.job_number

[Mincer]

Thanks.

Ok, I have altered the query slightly to:

$sql = "
	SELECT unbilled_item.job_number,
	unbilled_item.week_ending,
	SUM(unbilled_item.amount),
	job.project_manager
	FROM unbilled_item
	LEFT JOIN job ON (unbilled_item.job_number=job.job_number)
	GROUP BY unbilled_item.job_number
	ORDER BY unbilled_item.amount DESC
	" ;

Which appears to work fine.

However this:

$sql = "
	SELECT unbilled_item.job_number,
	unbilled_item.week_ending,
	SUM(unbilled_item.amount),
	job.project_manager
	FROM unbilled_item
	LEFT JOIN job ON (unbilled_item.job_number=job.job_number)
	GROUP BY job.project_manager
	ORDER BY unbilled_item.amount DESC
	" ;

Doesn't seem to do the ORDER BY bit???

[freakysid]

if you want to order by the summed values you just need to give your column an alias and use that in the order by clause:

$sql = "
	SELECT unbilled_item.job_number,
	unbilled_item.week_ending,
	SUM(unbilled_item.amount) AS totalAmount,
	job.project_manager
	FROM unbilled_item
	LEFT JOIN job ON (unbilled_item.job_number=job.job_number)
	GROUP BY unbilled_item.job_number
	ORDER BY totalAmount DESC
	" ;

[Mincer]

Actually, neither do any order by.

Is there any way to do that?

[Mincer]

freakysid and his telepathic powers strike again.

[Mincer]

Ok, is it also possible to extract data from the table 'user' at the same time in this query?

the field job.project_manager is relational to the field user.id

I want to get user.name to add to the page aswell.

Or do I have to do a separate query for that?