Mysql left join style sum()

Question

I believe the answer is already there at stackoverflow but I cannot find the right keywords. So please help.

Table sales looks like this:

state  | sales-representative        | product | sales
NY     | Mike B.                     | prod-A  | 90
FL     | David J.                    | prod-B  | 120 
FL     | Mike B.                     | prod-A  | 15

I need to get the total sales by such sales representative. Expected results for Mike B. look at this:

state  | product | sales
NY     | prod-A  | 90
FL     | prod-A  | 15
NY     | prod-B  | 0 <--How can I get this record as well?
FL     | prod-B  | 0

A regular sum query returns the first 2 records. How can I get the last 2 records as well?

 select state, product, sum(sales) 
      from sales 
      where sales-representative = 'Mike B.' 
       group by state, product

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — Sirmyself, Feb 19 '18 at 19:12
You can also see thos venn diagrams to visualise more easily : https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram — Sirmyself, Feb 19 '18 at 19:16
So, you want the total sales for a sales representative, for every combination of every product and state that appears anywhere in the table? — Uueerdo, Feb 19 '18 at 19:21

score 1 · Answer 1 · answered Feb 19 '18 at 19:24

SELECT ss.state, sp.product, SUM(sr.sales)
FROM (SELECT DISTINCT state FROM sales) AS ss
CROSS JOIN (SELECT DISTINCT product FROM sales) AS sp
LEFT JOIN sales AS r  
   AS sr ON ss.state = s.state 
         AND sp.product = s.product
         AND r.`sales-representative` = 'Mike B.'
GROUP BY ss.state, sp.product;

The cross join gets you every combination of state and product, and the left join gets you the specified representative's associated sales.

score 0 · Answer 2 · edited Mar 09 '18 at 06:52

0

You can use a left join on subquery for stated and product

   select t.state, t.product, sum(sales)
   from table1 
   left join (
    select t1.state, t2.product
    from t1 
    cross join  (
        select product 
        from t1
    ) t2

   ) t on t.state = table1.state and t.product = table1.product
   grooup by t.state, t.product

edited Mar 09 '18 at 06:52

marc_s

732,580
175
1,330
1,459

answered Feb 19 '18 at 19:14

ScaisEdge

131,976
10
91
107

Mysql left join style sum()

2 Answers2