I have the following four tables:

members

agent   |   memberid   |   firstname   |   lastname
===================================================
123     |    444       |  john         |    smith
123     |    555       |  sarah        |    stevens
123     |    777       |   harry       |    tabor

where agent refers to the selling agent id, member id is unique to every customer, first and last names associated with the member

selections

id   |   memberid   |   programid
=================================
1    |      444     |     1
2    |      444     |     2
3    |      444     |     3
4    |      555     |     1
5    |      555     |     2
6    |      555     |     3
7    |      777     |     1
8    |      777     |     2
9    |      777     |     3

which details the selections made by each member, for simplicity sake, each of the members listed above in this example selected to be a part of programID's 1, 2, and 3.

groups

programID  |   cost   |   type
===================================
  1        |   20.00  |     7
  2        |   30.00  |     8
  3        |   40.00  |     9

this table details the cost and system type for each unique programID

comp_levels

level  |   type1   |   type2   |   type3
============================================
   1   |   0.25    |   0.15    |  0.1
   2   |   0.3     |   0.18    |  0.11
   3   |   0.35    |   0.2     |  0.12
   4   |   0.4     |   0.225   |  0.15
   5   |   0.425   |   0.25    |  0.17
   6   |   0.45    |   0.27    |  0.2
   7   |   0.47    |   0.28    |  0.22
   8   |   0.5     |   0.3     |  0.24

this table highlights the sales commission structure for each particular agent based on their level and using the system type from GROUPS above.

For this example, we will assume Agent 123 is at commission level 2.

Ultimately, I am trying to build a reporting system which would detail the commission structure for both a single agent and a group of agents based on their sales, respective commission level, and the type of sale they brought in.

My current sql statement (which does not satisfy my need), is:

select
    a.memberid,
    a.firstname,
    a.lastname,
    sum(c.cost) as Cost,
CASE
    when c.type = '5' THEN (SUM(c.cost) * d.type1)
    when c.type = '6' THEN (SUM(c.cost) * d.type2)
    when c.type = '7' THEN (SUM(c.cost) * d.type3)
END as Commission
FROM
    members a
left join selections b using(memberid)
left join groups c using(programid)
left join comp_levels d on d.level = '2'
where agent = '123'
GROUP BY a.memberid;

the results of this query are:

memberid   |  firstname   | lastname   |   Cost   |   Commission
================================================================
   444     |   john       | smith      |   60.00  |   27.00
   555     |   sarah      | stevens    |   60.00  |   27.00
   666     |   harry      | tabor      |   60.00  |   27.00

The issue I face is the resultset is taking the first CASE match and applying to several rows returned when it should in fact only apply to each row, THEN group the results.

In the example above, the commission for each particular member SHOULD be $15.18 (30% of $20 + 18% of $30 + 11% of $40) but it is instead taking case 1 and app开发者_Python百科lying the 30% commission to every result (30% of $90).

I have played around with several variations but am unable to figure out how to combine what I am trying to do within a single query ( this query is ONLY run from a mysql gui so using any programming language is not acceptable.

I think the primary problem is that you're using a CASE improperly in a GROUP BY. I think most SQL platforms would complain if you try something like that, but MySQL will just give you bunk/random results and smile.

At any rate, I think you have to use a subquery to get what you want due to the unusual schema. Something like this

SELECT 
    sq.memberid, sq.firstname, sq.lastname, 
    SUM(sq.cost) AS total_cost, SUM(sq.commission) AS total_commission
FROM 
(
    SELECT 
    m.memberid, m.firstname, m.lastname, g.cost, 
    CASE
        WHEN g.type = '7' THEN (g.cost * cl.type1)
        WHEN g.type = '8' THEN (g.cost * cl.type2)
        WHEN g.type = '9' THEN (g.cost * cl.type3)
    END as commission
    FROM members m
    LEFT JOIN selections s USING(memberid)
    LEFT JOIN groups g USING(programid)
    LEFT JOIN comp_levels cl ON cl.level = '2'
    WHERE m.agent = '123' 
) sq GROUP BY sq.memberid

You can run the subquery by itself to trace what's going on.

Using your data, it gives the result

+----------+-----------+----------+------------+------------------+
| memberid | firstname | lastname | total_cost | total_commission |
+----------+-----------+----------+------------+------------------+
|      444 | john      | smith    |      90.00 |           15.800 |
|      555 | sarah     | stevens  |      90.00 |           15.800 |
|      777 | harry     | tabour   |      90.00 |           15.800 |
+----------+-----------+----------+------------+------------------+

NB: due to the unusual (lack of) normalization, i.e. mapping the types (7,8,9) to type fields (type1,type2,type3), this an awkward problem to wrap a head around. Moreover, I think you might have had some typos in your test data description; i.e. the type values in your SQL, and the total cost in your query output and desired result.