CODEWITHSUNDEEP
sqlaggregate
La Machine Infernale
La Machine Infernale

Reputation: 569

operation on aggregated values SQL

I want to sum my revenue by week, and my expenses by week.

select *
from (select sum(revenue) earned
            , week(transaction_date) earned_week

    from mytable

    where year(transaction_date) = '1988'

    group by week(transaction_date)


    ) moneyin


    inner join
        (select sum(expenses) spent
                  , week(expense_date) spent_week

          from mytable

          where year(expense_date) = '1988'

          group by week(expense_date)


          ) moneyout



    on moneyin.earned_week = moneyout.spent_week;

Now, I would like to add a column containing the difference between money in and money out (earned - spent AS balance), with the same weekly aggregation. but I don't seize at which level I can insert the statement. Any advice? Thank you

Upvotes: 1

Views: 44

Answers (2)

Radu
Radu

Reputation: 1031

You can use moneyin and moneyout data groups as any other table, so the query below should work:

select * , moneyin.earned - moneyout.spent as Difference
    from (select sum(revenue) earned
                , week(transaction_date) earned_week

        from mytable

        where year(transaction_date) = '1988'

        group by week(transaction_date)


        ) moneyin


        inner join
            (select sum(expenses) spent
                      , week(expense_date) spent_week

              from mytable

              where year(expense_date) = '1988'

              group by week(expense_date)


              ) moneyout



        on moneyin.earned_week = moneyout.spent_week;

Upvotes: 1

A. van Esveld
A. van Esveld

Reputation: 258

I would say you should be able to do that at the top of your statement instead of selecting *

So something like:

select earned week,
earned,
spent,
earned - spent as balance, 

from (select sum(revenue) earned
            , week(transaction_date) earned_week

    from mytable

    where year(transaction_date) = '1988'

    group by week(transaction_date)


    ) moneyin


    inner join
        (select sum(expenses) spent
                  , week(expense_date) spent_week

          from mytable

          where year(expense_date) = '1988'

          group by week(expense_date)


          ) moneyout



    on moneyin.earned_week = moneyout.spent_week;

Upvotes: 1

Related Questions