SQL with calculation from 2 tables's sum

user_14079792 0
Viewed 43

I have 2 tables Sales and return and i have two queries to get each day sales total and return total of certain month. it's result like this.

SELECT strftime('%m/%d', Date) as valDay, SUM(TotalPrice) as valTotal FROM Sales WHERE strftime('%m', Date) ='12' GROUP BY valDay;

Result

valDay  valTotal
12/01   50.0
12/02   50.0
12/04   50.0
12/05   120.0
12/06   160.0
12/07   30.0
12/08   80.0
12/09   50.0

and the second query is

SELECT strftime('%m/%d', Date) as valDay, SUM(TotalPrice) as valTotal FROM return WHERE strftime('%m', Date) ='12' GROUP BY valDay;

Result

valDay  valTotal
12/01   10.0
12/05   100.0
12/08   20.0

I want to deduct each day return total from same day sales. the result should be like this, ignore the brackets btw

valDay  valTotal
12/01   40.0 (50-10)
12/02   50.0
12/04   50.0
12/05   20.0 (120 - 100)
12/06   160.0
12/07   30.0
12/08   60.0 (80 - 20) 
12/09   50.0

is it possible to do with a single query ?

sqlite sql
3 Answers

You should use UNION ALL to get the resultset that includes all the rows of the 2 tables that you want and then aggregate only once:

SELECT strftime('%m/%d', Date) valDay, SUM(TotalPrice) valTotal
FROM (
  SELECT Date, TotalPrice 
  FROM Sales 
  WHERE strftime('%m', Date) ='12'
  UNION ALL
  SELECT Date, -TotalPrice  -- here you get the negative TotalPrice of the column
  FROM return
  WHERE strftime('%m', Date) ='12'
)
GROUP BY valDay

If you have data that expand to more than 1 year and all you want is the results for all Decembers of all years then this query will work.

But if you want the results for a particular year then you should also include the year in the WHERE clause, like:

WHERE strftime('%Y/%m', Date) = '2020/12'
user_104988280

One method is union all and aggregation:

SELECT valDay, SUM(sales) - SUM(returns)
FROM (SELECT strftime('%m/%d', s.Date) as valDay, SUM(s.TotalPrice) as sales, 0 as returns
      FROM Sales s
      WHERE strftime('%m', s.Date) = '12'
      GROUP BY valDay
      UNION ALL
      SELECT strftime('%m/%d', r.Date) as valDay, 0, SUM(r.TotalPrice) as valTotal
      FROM return r
      WHERE strftime('%m', r.Date) = '12'
      GROUP BY valDay
     ) sr
GROUP BY valDay
user_11440350

Simply, use the LEFT JOIN between two of your query and use the expression to calculate the result as follows:

select s.valDay, s.valTotal -r.valTotal from
(SELECT strftime('%m/%d', Date) as valDay, SUM(TotalPrice) as valTotal FROM Sales WHERE strftime('%m', Date) ='12' GROUP BY valDay) s
left join
(SELECT strftime('%m/%d', Date) as valDay, SUM(TotalPrice) as valTotal FROM return WHERE strftime('%m', Date) ='12' GROUP BY valDay) r
on s.valDay = r.valDay
user_115656290
Related