ajlisowski Posted September 9, 2011 Share Posted September 9, 2011 So I need to have a query that grabs some sales figures by salesman. It would need to report back the total opportunities and also how many of those were sold. If I just wanted to get the amount sold I could do the followng SELECT `lret_retailsalesman_id_c`, count(`id`) FROM `lret_retailopportunity` WHERE `status`='sold' But how would I do a query that returns both the amount sold and the total amount in one line? Quote Link to comment Share on other sites More sharing options...
ajlisowski Posted September 9, 2011 Author Share Posted September 9, 2011 Ah, Im guessing I would have to do a count(distinct blah) and then use joins to get both the sold and the total. Is this the correct method or am I making things way too difficult? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 9, 2011 Share Posted September 9, 2011 Just add a SUM() and a group by. Quote Link to comment Share on other sites More sharing options...
ajlisowski Posted September 9, 2011 Author Share Posted September 9, 2011 Maybe I asked the question wrong... basically I have a table of opportunities, these can either be sold, lost or open. Opportunity Status Salesman ID 1 Open 120 2 Sold 120 3 Lost 120 I would want a query that would get me the following row as a return Total Opportunities Closed Opportunities Lost Opportunities 3 2 1 So I would need to group by salesman ID and then count the total number of opportunities, the total number of opportunities which are either sold or lost, then the total number of opportunities which are lost. Right now I am doing so with the following query SELECT s.`id`, count(DISTINCT open.`id`)+ count(DISTINCT sold.`id`)+ count(DISTINCT lost.`id`) AS `total` count(DISTINCT sold.`id`)+ count(DISTINCT lost.`id`) AS `closed` count(DISTINCT lost.`id`) AS `lost` FROM `salesman` AS `s` JOIN `opportunities` AS `open` ON (open.`salesman_id`=s.`id` AND (open.`status`!='sold' AND open.`status`!='lost')) JOIN `opportunities` AS `sold` ON (sold.`salesman_id`=s.`id` AND sold.`status`='sold' ) JOIN `opportunities` AS `lost` ON (lost.`salesman_id`=s.`id` AND lost.`status`='lost') GROUP BY s.`id` It works but it seems like a roundabout way of accomplishing what I want. But I can not think of any other way, cause I want to pretty much have a where statement within my count, and the only way i can think of doing that is with a join. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 9, 2011 Share Posted September 9, 2011 You're supposed to use SUM() and IF(). e.g. SUM( IF( `status` = 'sold', 1, 0 ) ) Quote Link to comment Share on other sites More sharing options...
ajlisowski Posted September 9, 2011 Author Share Posted September 9, 2011 Wow...yeah thats a lot simpler then what I did. Hah. I feel foolish for not thinking of that, thanks a bunch. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.