SQL Count and Grouping

Question

Ok, I'm working with a nested SQL statement:

I want to query the last 120 proposals written by our salesmen. I'm using a nested query to get the last 120 proposals out of the system, and then filter them based on status, etc...

The problem I'm having is how do I get the count of proposals for each salesman?

The SQL is obviously wrong, but here's where I'm stuck.

SELECT 
   CASE userId 
    WHEN '4' THEN 'AT'
    WHEN '3' THEN 'EO'
    WHEN '11' THEN 'CT'
    WHEN '13' THEN 'MH'
    ELSE userId
END AS Salesman,
SUM(contractAmt) as 'Contract Total',
AVG(DATEDIFF(contractDate, proposalDate)) AS averageDays,
SUM(proposalAmt) as pTot,
Count(*) as Contracts,
Count(A.proposalAmt) as Proposals,
(SUM(contractAmt) / SUM(proposalAmt)) AS 'Hit Rate $s',
(Count(*) / Count(A.proposalAmt)) AS 'Hit Rate #s'
FROM
( /* Get the last 120 proposals not in Lead or Proposal status*/
SELECT contractAmt, proposalAmt, contractDate, status, userId, 
    CASE WHEN proposalDate = '0000-00-00'   
        THEN CAST(contractDate as Date)
        else CAST(proposalDate as Date)
    END as proposalDate
FROM project
WHERE (status != 'proposal' and status != 'lead')
/*GROUP BY id*/
ORDER BY contractDate DESC
LIMIT 0, 120) A
WHERE status = 'contract' or status = 'complete'
GROUP BY userId

"Count(A.proposalAmt) as Proposals," this gives me the filtered number of records. (Only contracts and completed orders)

Within the nested bit, the query doesn't want to return a one line count with 100 individual records. If I group by userId, I get a count of proposals by user, but now I don't have the individual records for later filtering.

There's a few ways I can think of to get around this, but all of them suck. I could just do another query, but that's inelegant and doesn't work that well with my output.

Database is MySql.

Workflow in case I'm not explaining it correctly:

For each user ID
Get the last 100 proposals
Count # of proposals by salesman
Total proposal $ amount by salesman
Count # of contract or complete proposals
Total $ amount of contract and complete jobs
# proposals / # contracts = Hit Rate #'s
$ proposals / $ contracts = Hit Rate $'s
AVERAGE(contract date - proposal date) = Average # days in process

Output should be similar to:

   Salesman  Avg Days  ProposalTot  ContractTot  HR $'s  Prop #  Con #  HR#'s
   --------------------------------------------------------------------------------------
   EO       |   29.27 |  $30,000  |    $15,000  |  50%  |   30  |   15 | 50%

EDIT: Added Schema

Table project
=============
id, userId, clientId, contactId, projectName, status, description, creationDate, shipDate, estimateAmt, leadAmt, reestimateAmt, proposalAmt, contractAmt, completeAmt, type, subType, estDate, reestDate, proposalDate, contractDate, completeDate, lostDate, onHoldDate, estShip, reestShip, proposalShip, contractShip, completeShip, casperLink, statusChangeTS
-------------
id               int(11) PK
userId           int(11)
clientId         int(11)
contactId        int(11)
projectName      varchar(255)
status           enum('lead','proposal','contract','complete','onHold','lost')
description      text
creationDate     date
shipDate         date
estimateAmt      int(11)
leadAmt          int(11)
reestimateAmt    int(11)
proposalAmt      int(11)
contractAmt      int(11)
completeAmt      int(11)
type             varchar(100)
subType          varchar(100)
estDate          date
reestDate        date
proposalDate     date
contractDate     date
completeDate     date
lostDate         date
onHoldDate       date
estShip          date
reestShip        date
proposalShip     date
contractShip     date
completeShip     date
casperLink       varchar(20)
statusChangeTS   date

Answer 1

Try this:

SELECT 
    Count(*) as 'Total Proposals',
    SUM(CASE WHEN status = 'complete' or status = 'contract' THEN 1 ELSE 0 END) as 'Total Contracts',
    CASE userId 
        WHEN '4' THEN 'AT'
        WHEN '3' THEN 'EO'
        WHEN '11' THEN 'CT'
        WHEN '13' THEN 'MH'
        ELSE userId
    END AS Salesman,
    SUM(CASE WHEN status = 'complete' or status = 'contract' THEN contractAmt ELSE 0 END) as 'Contract Total',
    AVG(DATEDIFF(contractDate, proposalDate)) AS averageDays,
    SUM(proposalAmt) as pTot,
    (SUM(CASE WHEN status = 'complete' or status = 'contract' THEN contractAmt ELSE 0 END) / SUM(proposalAmt)) AS 'Hit Rate $s',
    (SUM(CASE WHEN status = 'complete' or status = 'contract' THEN 1 ELSE 0 END) / Count(*)) AS 'Hit Rate #s'
FROM
    (/* This inner loop only selects the top 100 records */
    select * FROM project
    WHERE (status != 'proposal' and status != 'lead') and userId = '13'
    order by contractDate DESC
    LIMIT 0, 30) A
GROUP BY userId 

The answer I was able to devise was to not filter using a WHERE clause, but to use a CASE clause to filter the records that have a contract. You'll need to do this where ever you need the Contract Total, and you'll have to filter the Contract Amounts to only those with your status. (Unless your DB already takes care of that.)

Total Proposals | Total Contracts | Salesman | Contract Total | averageDays | pTot   | Hit Rate $s | Hit Rate #s
30                15                MH         160496           46.8          324122   0.4952        0.5