SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this query possible??

    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.

    Code:
    	$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)
    	" ;

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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

  3. #3
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    Ok, I have altered the query slightly to:

    Code:
    $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:

    Code:
    $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???
    Last edited by Mincer; Jan 24, 2002 at 07:27.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    $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
    	" ;

  5. #5
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, neither do any order by.

    Is there any way to do that?

  6. #6
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freakysid and his telepathic powers strike again.

  7. #7
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •