CODEWITHSUNDEEP
mysqljoin
Harry Gohil
Harry Gohil

Reputation: 103

SQL sum aggregate including 0

I have two tables. One is an employee table containing the employee information and the other table is the sales the employee has made. I am trying to group the sum of sales made by an employee in a month including the months where he/she has made zero sales. Here are some of the values in the table

Employees table

number             name
1                  Matt
2                  Foggy
3                  Karen
4                  Wilson

sales

employee_number           month          sale_number          sale_amount
1                         January 2015       1                    300
1                         January 2015       2                     50
1                         February 2015      1                     400
2                         March 2015         1                     300
3                         January 2015       1                     50

I was able to write the query for getting the monthly sales sum using the following query

select sum(sales.sale_amount), sales.employee_number, sales.month, sales.sale_number from sales group by employee_number, month;

Now because I also need the months including zeros I thought left outer join with the different months should do the trick. However the output still consists of the same output from before without zeros or null. Does the left outer join not join the null values?

The output should be something like this. number name sale_amount sale_month 1 Matt 350 January 2015 1 Matt 400 February 2015 1 Matt 0 March 2015 2 Foggy 0 January 2015 2 Foggy 0 February 2015 2 Foggy 300 March 2015 and so on.

Upvotes: 1

Views: 2039

Answers (2)

Andomar
Andomar

Reputation: 238088

A left outer join conjures up null values for missing rows from the right hand table. To show all months, you'd switch the order of tables:

from    months
cross join
        employees
left outer join 
        sales 
on      sales.month = months.month
        and employees.number = sales.employee_number

If you're missing a months table, you can ad-lib one from the sales table:

select  months.month
,       employees.name
,       sum(sale_amount) as sales
from    (
        select  distinct month
        from    sales
        ) as months
cross join
        employees
left outer join 
        sales 
on      sales.month = months.month
        and employees.number = sales.employee_number
group by
        months.month
,       employees.name

Example at SQL Fiddle.

Upvotes: 1

Dharmesh Patel
Dharmesh Patel

Reputation: 1891

Following query will help you:

SELECT q1.month, q1.number, q2.total_sales FROM
    (SELECT distinct s.month, e.number 
        FROM sales s, Employees e) AS q1
    LEFT JOIN
    (SELECT SUM(sales.sale_amount) AS total_sales, sales.employee_number, sales.month, sales.sale_number 
        FROM sales group by employee_number, month) AS q2
    ON (q1.month = q2.month) AND (q1.number = q2.employee_number) 
ORDER BY month, number;

check the fiddle

Upvotes: 0

Related Questions