MySQL Query GroupingSubQuery Question

Question

Hello, I am having trouble getting my head around this query for some reason and I was wondering if someone would be able to help me out here. I have the following 3 tables:

opp_cstm:

id_c  make_c      time_followup_c  lead_category_c  lead_type_c  
9     GMC          224             GM               Internet Sales  
e     Buick     809             GM Internet      Service
8     GMC          1559            Dealer Web       Sales
2     Cadillac     10596           Dealer Web       Service
3     Chevrolet    15595           GM Internet      Sales
4     Chevrolet    905           GM Internet      Service

opportunities:

id  date_entered            deleted
2   2010-07-16 16:46:21      0
3   2010-07-16 16:55:53      0
4   2010-07-16 19:30:12      0
8   2010-07-16 16:44:13      0
9   2010-07-16 16:39:17      0
e   2010-07-16 16:41:44      0

leads_objectives:

makes_carried   resp_time_obj
GMC           18000
Ford             7200
Cobalt         43200
Chevrolet       18000
Buick           18000
Cadillac         7200

I am needing to get the following layout (this will obviously be grouped by date, LCat):

Date    LCat          LType     #ofLds   AvgResp       #LdsRespOT   %LdsRespOT  #Lds!RespOT %Lds!RespOT
19-Jul  GM Internet   Sales     10       18 minutes     7          70%          3          30%
19-Jul  GM Internet   Service   20       20 minutes     10        50%          10         50%
19-Jul  Handraiser    Sales     10       45 minutes     5          50%          5          50%
20-Jul  Dealer Web    Sales     20       120 minutes    5          25%          15         75%
20-Jul  Dealer Web    Service   10       7 minutes    3           30%          7          70%

Explanation of each column I need:

Date: opportunities.date_entered = today (this needs to be on everything of course)

LCat: opp_cstm.lead_category

LType: opp_cstm.lead_type

#ofLds: this needs to be the Count of opportunities where deleted = "0" and Lead Category is not null

AvgResp: Avg. of timefollowup-C field in opportunities where Deleted = "0" and Lead Category is not null and and time_followup_c > 0 and not null

#LdsRespOT: Count of opportunities where Deleted = "0" AND Lead Category is not null AND time_followup_c is less than or equal to resp_time_obj AND make_c = makes_carried and time_followup_c > 0 and not null

%LdsRespOT: (#LdsRespOT / #ofLds)

#Lds!RespOT: (#ofLds - #LdsRespOT)

%Lds!RespOT: (#Lds!RespOT / #ofLds)

I am having a hard time getting my head around this query. I was wondering if someone on here could provide some assistance of some sort with this one? How would I write this query correctly?

I have tried several times but fail every time and I am getting frustrated! I know I am just missing some grouping of some kind or some kind of sql subquery that I am missing.

Any help would be greatly appreciated!

Thanks!

Answer 1

For anyone who comes across this that might need help with something like this, here is what I ended up doing:

SELECT 
opportunities.date_entered as Date,
opportunities_cstm.lead_category_c as LCat,
opportunities_cstm.lead_type_c as LType,
count(opportunities.id) as '# of Lds',
SUM(opportunities_cstm.time_followup_c)/count(opportunities.id) as AvgResp,
SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ) as '#LdsRespOT',
(SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ) /count(opportunities.id))*100 as '%LdsRespOT',
count(opportunities.id) - SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    )as '#Lds!RespOT',
((count(opportunities.id) - SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ))/count(opportunities.id))*100 as '%Lds!RespOT'
FROM
opportunities
INNER JOIN 
opportunities_cstm 
ON 
    opportunities_cstm.id_c = opportunities.id
AND
    opportunities_cstm.lead_category_c IS NOT NULL
AND
    opportunities_cstm.lead_category_c NOT LIKE ''
INNER JOIN
 leads_handling_objectives
ON
    leads_handling_objectives.makes_carried = opportunities_cstm.make_c
WHERE
opportunities.date_entered = DATE(NOW())
AND 
opportunities.deleted='0'
GROUP BY 
opportunities_cstm.lead_category_c