根据group by划分列值（数字）

Question

First of all I am not really good with MySQL whatever experience I have I am putting it to make this query

In my query the main problem is with

        left join subdealers as subdealer
            ON 
                (
                    employees.Salesman1Number = subdealer.employee_number 
                    OR employees.Salesman2Number = subdealer.employee_number
                    OR employees.Salesman3Number = subdealer.employee_number
                )

I am trying to get the FrontGross, BackGross etc grouped by subdealer.group_name the problem is Saleman1Number & Salesman2Number might belong to same group_name and in the query below it counts them as two different Salesmen while what I want them to count as one in case the Salesman1Number, Saleman2Number and Salesman2Number belongs to same subdealer.group_name

For example: Salesman1Number belongs to group_name Fleet and Salesman2Number also belongs to Fleet

They both contributed to sell a single car. Now they both have half credit of what they sold and that credit goes to group_name Fleet as one, half from Salesman1Number and half from Salesman2Number

currently the query I wrote doesn't divide them in half depending on their group_name but count it as one from Salesman1Number and one from Salesman2Number

SELECT count(core_leads.core_id) as leads,
            count(new.id) as new,    
            count(used.id) as used,    
            IFNULL(SUM(profit.FrontGross) + SUM(finance.HoldbackAmount), 0) as FrontGross,
            IFNULL(SUM(profit.BackGross) + SUM(profit.FinanceReserve), 0) as BackGross,
            IFNULL(SUM(profit.TotalProfit), 0) as TotalProfit,
            IFNULL(SUM(finance.HoldbackAmount), 0) as HoldbackAmount,
            IFNULL(SUM(finance.Holdcheck), 0) as Holdcheck,
            IFNULL(subdealer.group_name, 'Others') as group_name
            from core_leads 
            inner join 
                (
                    select * from closed_deals 
                    right join 
                        (
                            select ContractDate, id as infoId, closed_deal_id 
                            from closed_deal_infos
                        ) as info
                    ON closed_deals.id = info.closed_deal_id
                    AND DATE(info.ContractDate) BETWEEN '2014-01-01' AND '2017-01-01'
                ) as closed
            ON core_leads.core_id = closed.core_lead_id
            AND core_leads.type != 'Unwind'
            AND core_leads.type != 'Canceled'
            left join closed_vehicles as used
            ON closed.id = used.closed_deal_id
            AND used.NewUsed = 'U'
            left join closed_vehicles as new
            ON closed.id = new.closed_deal_id
            AND new.NewUsed = 'N'
            left join closed_dealer_employees as employees
            ON closed.id = employees.closed_deal_id
            left join subdealers as subdealer
            ON 
                (
                    employees.Salesman1Number = subdealer.employee_number 
                    OR employees.Salesman2Number = subdealer.employee_number
                    OR employees.Salesman3Number = subdealer.employee_number
                )
            AND 
                (
                    subdealer.group_name = 'Fleet'
                    OR subdealer.group_name = 'Internet'
                    OR subdealer.group_name = 'Sales'
                )
            left join closed_profit as profit
            ON closed.id = profit.closed_deal_id
            left join closed_finance as finance
            ON closed.id = finance.closed_deal_id
            group by subdealer.group_name

This results this

While in the Fleet dept column name leads should be 38 instead of 40 because it is counting two different Salesmen whom belongs to same group_name as two

Let me know if I was not clear enough

Answer 1

To simplify your example i will use only two tables.

persons:

| personId | groupId |
|----------|---------|
|        1 |       1 |
|        2 |       2 |
|        3 |       2 |
|        4 |       3 |
|        5 |       4 |
|        6 |       5 |

activities:

| actId | person1Id | person2Id | person3Id | actValue |
|-------|-----------|-----------|-----------|----------|
|     1 |         1 |         2 |         3 |        1 |
|     2 |         1 |         2 |         4 |       10 |
|     3 |         5 |    (null) |    (null) |      100 |

A query which matches your problem would be:

select 
  p.groupId, count(a.actId) numActs, sum(a.actValue) sumVals, group_concat(a.actId) as acts
from activities a
left join persons p on (
  a.person1Id = p.personId or
  a.person2Id = p.personId or
  a.person3Id = p.personId
)
group by p.groupId;

Result:

| groupId | numActs | sumVals |  acts |
|---------|---------|---------|-------|
|       1 |       2 |      11 |   1,2 |
|       2 |       3 |      12 | 1,2,1 |
|       3 |       1 |      10 |     2 |
|       4 |       1 |     100 |     3 |

For the group with groupId=2 we have counted three activities (1,2,1). The Activity with actId=1 is counted twice because there are two persons from same group. To prevent that, we can define that a row for person2 should not be counted (should be filtered out) if person1 is from same group. And a row for person3 should not be counted if person1 or person 2 is from the same group. This can be done in the WHERE clause with dependent selects:

select 
  p.groupId, count(a.actId) numActs, sum(a.actValue) sumVals, group_concat(a.actId) as acts
from activities a
left join persons p on (
  a.person1Id = p.personId or
  a.person2Id = p.personId or
  a.person3Id = p.personId
)
where (p.personId = a.person1Id
  ) or (
    p.personId = a.person2Id and
    p.groupId not in (select groupId from persons where personId = a.person1Id)
  ) or (
    p.personId = a.person3Id and
    p.groupId not in (select groupId from persons where personId in (a.person1Id, a.person2Id))
  )
group by p.groupId;

Result:

| groupId | numActs | sumVals | acts |
|---------|---------|---------|------|
|       1 |       2 |      11 |  1,2 |
|       2 |       2 |      11 |  1,2 |
|       3 |       1 |      10 |    2 |
|       4 |       1 |     100 |    3 |

http://sqlfiddle.com/#!9/604a5/1

Note: If possible - you should consider to normalize your tables.