CODEWITHSUNDEEP
sqlpostgresql
Bob McBobson
Bob McBobson

Reputation: 914

Why is my SQL query with aggregate function returning the wrong values to me when running in larger query?

I am running a SQL query in order to calculate the monthly income and costs for some company. The values I am using to calculate the income is based on a table that lists the sales, while the costs are based on a table that lists all employees and their annual salaries. So, in order to calculate the monthly costs, I summed all the employees' annual salaries and divided them by 12. In order to calculate the monthly incomes, I grouped the sales by the month in which they were made and summed them up. The result would have three columns: the year/month, the monthly income, and the monthly cost.

However, there is something wrong with values in the monthly cost column. They all should be 8145, but instead, they are as follows:

year/month  monthly_income monthly_cost
2018-04     9300           2937
2018-03     3855           3000
2018-01     7875           2937

While the monthly income values are correct, the monthly cost values are incorrect. This result is based on the following SQL command:

SELECT 
    TO_CHAR(s.sale_date, 'YYYY-MM') AS year_month,
    (SUM(s.amount)) AS monthly_income,
    (SUM(e.annual_salary)/12) AS monthly_cost
FROM employees e
INNER JOIN sales s ON e.id = s.employee_id
GROUP BY year_month;

But what is more confusing is if I run this command but simplified to only calculating the sum of the employee's annual salaries divided by 12, I get the correct answer of 8145.

SELECT 
    (SUM(e.annual_salary)/12) AS monthly_cost
    from employees e;

So what is going on here? Any help would be appreciated. I have so far tried changing the formatting of the aggregate function, but to no avail.

Upvotes: 1

Views: 155

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Employees can have zero or more sales per months, so I wouldn't expect the numbers to add up. Doing what you want is a bit complicated. Getting the sales is simple:

SELECT TO_CHAR(s.sale_date, 'YYYY-MM') AS year_month,
       SUM(s.amount) AS monthly_income
FROM employees e INNER JOIN
     sales s
     ON e.id = s.employee_id
GROUP BY year_month;

However, to get the salary of the employees is more complicated. I think the best route is two levels of aggregation:

SELECT year_month,
       SUM(employee_sales) AS monthly_income,
       SUM(annual_salary / 12) as salary
FROM (SELECT e.id, e.annual_salary, TO_CHAR(s.sale_date, 'YYYY-MM') as year_month, 
             SUM(s.amount) as employee_sales
      FROM employees e INNER JOIN
           sales s
           ON e.id = s.employee_id
      GROUP BY e.id, e.annual_salary, year_month
     ) es
GROUP BY year_month;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try the below -

SELECT 
    TO_CHAR(s.sale_date, 'YYYY-MM') AS year_month,
    (SUM(s.amount)) AS monthly_income,
    (select SUM(annual_salary) from employees)/12 AS monthly_cost
FROM employees e
INNER JOIN sales s ON e.id = s.employee_id
GROUP BY year_month;

Upvotes: 1

Related Questions