List Roles that sum of expenses are larger than average-CodePudding

Sorry for asking this simple question(Maybe?). I am a newbie in SQL and I am trying to figure out how to solve this problem on my own.

I have three different tables but they share the same primary IDs.

L Table looks like this

L_Id  L_Nam
L1     GA
L2     FL
L3     NC

The R Table looks like this

R_Id  R_Name
R1     Same
R2     Steve
R3     Tony
R4     Roy
R5     Carol
R6     Jane
R7     Emma

The E Table looks like this

L_Id  R_Id      E_Date          E_Amt
L1     R1     2019-08-02         100
L1     R1     2019-08-05         400
L1     R1     2019-08-11         50
L2     R2     2019-08-02         150
L2     R2     2019-08-01         400
 :      :         :               :
L3     R6     2019-08-21         1020
L3     R7     2019-08-29         2000

There are two goals that I am trying to solve.

Calculate the sum of E_Amt from the E table base on the month from E_Date and L_Name from the L table then list the R_Name
Only list the R_Name that is greater or equal to the Average Monthly expense of any location they had expenses in that month.

I am still stuck on my first task. Here is my approach

Select 
R.R_Id,R.R_Name,
SUM(E.E_Amt),
month(E.E_Date)
from L inner join E on
L.L_Id = E.L_Id
inner join R on
R.R_Id = E.R_Id
GROUP By E.R_Id, month(E.E_Date);

But my query isn't working so I am wondering what did I do wrong and what approach I need to take in order to solve the goals.

Thanks!

CodePudding user response：

For the first question,

Calculate the sum of E_Amt from the E table base on the month from E_Date and L_Name from the L table then list the R_Name

Try:

select 
           r.Role_Name,
           a.month_of_date,
           sum(a.sum_expenses) as total_sum
    from (       
      select l.Location_Name,
             e.Location_Id,
             month(e.Transaction_Date) as month_of_date,
             e.Role_Id,
             sum(Expense_Amt)  as sum_expenses
    from Expenses e 
    inner join Locations l
    on e.Location_Id = l.Location_Id
    group by month_of_date,e.Location_Id,l.Location_Name,e.Role_Id 
      ) as a inner join Roles r on a.Role_Id=r.Role_Id
      group by r.Role_Name,a.month_of_date ;

select r.Role_Id,
       r.Role_Name,
       a.month_of_date,
       a.sum_expenses
from (       
  select l.Location_Name,
         e.Location_Id,
         month(e.Transaction_Date) as month_of_date,
         e.Role_Id,
         sum(Expense_Amt)  as sum_expenses
from Expenses e 
inner join Locations l
on e.Location_Id = l.Location_Id
group by month_of_date,e.Location_Id,l.Location_Name,e.Role_Id 
  ) as a inner join Roles r on a.Role_Id=r.Role_Id;

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/3

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/2

As per second question, if I understood correctly, try:

Only list the R_Name that is greater or equal to the Average Monthly expense of any location they had expenses in that month.

select r.Role_Name 
from Roles r
inner join 
(
  select 
       Role_Id,
       avg(Expense_Amt) as average_per_month,
       sum(Expense_Amt) as total_sum_per_month,
       Locations.Location_Id          
from Expenses 
inner join Locations  on Expenses.Location_Id=Locations.Location_Id
group by month(Transaction_Date),Role_Id,Locations.Location_Id
) as a on r.Role_Id=a.Role_Id
where average_per_month<=total_sum_per_month
group by r.Role_Name ;

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/7

CodePudding user response：

You have a query that gives you the sums per month and role. This is the base you build your query on that compares month's role sum with the month's average sum.

with my_base as
(
  select 
    r.role_id, r.role_name,
    sum(e.expense_amt) as sum_expense_amt,
    date_format(e.transaction_date, '%Y-%m') as month
  from locations l
  inner join expenses e on e.location_id = l.location_id
  inner join roles r on r.role_id = e.role_id
  group by r.role_id, date_format(e.transaction_date, '%Y-%m')
)
select *
from my_base
where sum_expense_amt >=
(
  select avg(sum_expense_amt)
  from my_base all_roles
  where all_roles.month = my_base.month
);

Or make this two subqueries in the WITH clause. Here is an example with a join. (I don't like the join much, I prefer a subquery like above, because conditions belong in the WHERE clause in my opinion, not in the FROM clause. But why not show alternatives ;-)

with my_base as
(
  select 
    r.role_id, r.role_name,
    sum(e.expense_amt) as sum_expense_amt,
    date_format(e.transaction_date, '%Y-%m') as month
  from locations l
  inner join expenses e on e.location_id = l.location_id
  inner join roles r on r.role_id = e.role_id
  group by r.role_id, date_format(e.transaction_date, '%Y-%m')
)
, averages as
(
  select month, avg(sum_expense_amt) as avg_sum_expense_amt
  from my_base
  group by month
)
select my_base.*
from my_base
join averages on averages.month = my_base.month
             and averages.avg_sum_expense_amt <= my_base.sum_expense_amt
;

Or use a window function. This is my preferred way of writing the query.

select role_id, role_name, sum_expense_amt, avg_sum_expense_amt, month
from
(
  select 
    r.role_id, r.role_name,
    sum(e.expense_amt) as sum_expense_amt,
    avg(sum(e.expense_amt)) over (partition by date_format(e.transaction_date, '%Y-%m'))
      as avg_sum_expense_amt,
    date_format(e.transaction_date, '%Y-%m') as month
  from locations l
  inner join expenses e on e.location_id = l.location_id
  inner join roles r on r.role_id = e.role_id
  group by r.role_id, date_format(e.transaction_date, '%Y-%m')
) aggregated
where sum_expense_amt >= avg_sum_expense_amt;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c4de13ea1946c6542e5ca4631d5d658c

All of these queries require at least MySQL 8, which should not be a problem because that version is aready three years old. In older versions neither CTEs (the WITH clause) nor analytic functions are featured. (In those old versions you'd have to write the base subquery twice.)