SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  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)

    Can I combine these queries?

    I have a table that has various columns relating to different charges to a job. The final column (standard_sched) contains a total for each row including any tax etc applied to that row.

    Now, if there is anything specified in the labout amount column, I want to sum the total column for each of those rows on the table. eg:

    Code:
    SELECT
    	SUM( standard_sched ) AS labour_revenue
    FROM
    	jrs
    WHERE
    	labour_amt > 0
    AND
    	job_number = '$job'
    Similarly, i want to do the same thing for 3 other columns:

    Code:
    SELECT
    	SUM( standard_sched ) AS equip_revenue
    FROM
    	jrs
    WHERE
    	equip_amt > 0
    AND
    	job_number = '$job'
    ...etc.

    Each of these columns are mutually exclusive, so if there is any entry in labour amount, there will be no entry in equipment amount.

    Is there a way to efficiently combine these?

    Thanks,

    Matt.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select sum( case when labour_amt > 0 
                     then standard_sched
                     else 0 end ) AS labour_revenue
         , sum( case when equip_amt > 0 
                     then standard_sched
                     else 0 end ) AS equip_revenue
      from jrs
     where ( labour_amt > 0 or equip_amt > 0 )
       and job_number = '$job'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  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 Rudy.


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
  •