I need the sum of two customers each so I wrote the following query:
SELECT m.m_name, m2.m_name AS m_name2, SUM(m.price + m2.price) as total_amount, rn.name
FROM
(
SELECT m.name AS m_name, m.id AS m_id ,IFNULL(SUM(d.num * p.price), 0) AS price
FROM m_member as m
LEFT JOIN t_sales AS t ON m.id = t.m_id
INNER JOIN m_product as p
LEFT JOIN t_sales_detail as d ON t.id = d.id AND p.id = d.p_id
GROUP BY m.id
)AS m
LEFT JOIN
(
SELECT m.name AS m_name, m.id AS m2_id ,IFNULL(SUM(d.num * p.price), 0) AS price
FROM m_member as m
LEFT JOIN t_sales AS t ON m.id = t.m_id
INNER JOIN m_product as p
LEFT JOIN t_sales_detail as d ON t.id = d.id AND p.id = d.p_id
GROUP BY m.id
) AS m2 ON m.m_id != m2.m2_id
JOIN m_lank AS rn ON (SELECT SUM(m.price + m2.price)) BETWEEN rn.low_limit AND rn.up_limit
GROUP BY m.m_id, m2.m2_id
order by total_amount;
The results looks like this:
# m_name m_name2 total_amount name
Nagayama Kawata 380 Bronze
Kawata Nagayama 380 Bronze
Nagaoku Kawata 500 Bronze
Kawata Nagaoku 500 Bronze
Nagayama Nagaoku 880 Bronze
Nagaoku Nagayama 880 Bronze
Kawashima Kawata 2620 Bronze
Kawata Kawashima 2620 Bronze
...
The problem is, I don't need to sum the same customer two times. How can I get rid of the duplicate results? Anyone has a solution?
EDIT: I want something like this:
# m_name m_name2 total_amount name
Nagayama Kawata 380 Bronze
Nagaoku Kawata 500 Bronze
Nagayama Nagaoku 880 Bronze
Kawashima Kawata 2620 Bronze
...
Sorry for my poor English. It might be difficult to understand what I need.
Basically I have in total five customers. Customer A has bought a total of 400$ Customer B has bought a total of 1030$. Customer C .... 1540$. Customer D .... 0$. Customer F .... 320$.
Now I want to sum every customer with each other:
A + B
A + C
A + D
A + F
B + C
B + D
B + F
C + D
C + F
D + F
If I understood you correctly, it can be achieved with the minimal change to your query. Just replace != with < or >. Thus you will get each pair only once.
SELECT m.m_name, m2.m_name AS m_name2, SUM(m.price + m2.price) as total_amount, rn.name
FROM
(
SELECT m.name AS m_name, m.id AS m_id ,IFNULL(SUM(d.num * p.price), 0) AS price
FROM m_member as m
LEFT JOIN t_sales AS t ON m.id = t.m_id
INNER JOIN m_product as p
LEFT JOIN t_sales_detail as d ON t.id = d.id AND p.id = d.p_id
GROUP BY m.id
)AS m
LEFT JOIN
(
SELECT m.name AS m_name, m.id AS m2_id ,IFNULL(SUM(d.num * p.price), 0) AS price
FROM m_member as m
LEFT JOIN t_sales AS t ON m.id = t.m_id
INNER JOIN m_product as p
LEFT JOIN t_sales_detail as d ON t.id = d.id AND p.id = d.p_id
GROUP BY m.id
) AS m2 ON m.m_id > m2.m2_id
JOIN m_lank AS rn ON (SELECT SUM(m.price + m2.price)) BETWEEN rn.low_limit AND rn.up_limit
GROUP BY m.m_id, m2.m2_id
order by total_amount;