Skip navigation links

MySQL Forums :: Newbie :: left join on same table use different "where" clause


Advanced Search

left join on same table use different "where" clause
Posted by: Jim DiGiovanni ()
Date: February 10, 2013 04:50PM

I have 1 table CPMast that contains some fields.
I want to know the percentage of completed work each contractor has done.

The table CPmast has these fields:
Contractor (char)
Status (char,1)
Reportdate (date)

I want the final data to be this:

contractor, jobs, completed, Percent
TNC 383 60 15.6 %
DMC 194 38 19.5%
BBC 181 14 7.7%
CBG 148 64 43.2%


The only way I can think to do it is 2 queries.
1 to select all the jobs that began in 2013
2 to select all jobs that began in 2013 with complete status

query 1
SELECT contractor, count(contractor) FROM `cpmast` WHERE reportdate>='2013-01-01' group by contractor

query 2

SELECT contractor, count(contractor) FROM `cpmast` WHERE reportdate>='2013-01-01' AND status="C" group by contractor

How do I left join so all contractors are shown and each of the 3 remaining fields are displayed?

Thanks for any assistance



Edited 1 time(s). Last edit at 02/10/2013 06:05PM by Jim DiGiovanni.

Options: ReplyQuote


Subject Written By Posted
left join on same table use different "where" clause Jim DiGiovanni 02/10/2013 04:50PM
Re: left join on same table use different "where" clause Peter Brawley 02/10/2013 07:28PM
Re: left join on same table use different "where" clause Jim DiGiovanni 02/10/2013 11:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.