On the Firebird 2.5 server in the database there are tables: goods, income, sale.

  • The Goods table has columns: goods_id, name
  • Income: Income_id, Goods_id, incomeqty (quantity), recdate
  • Sale: sales_id, income_id, Goods_id, saleqty, recdate

There is a query that selects incomes, expenses and balances (difference):

select income.goods_id, sum(case when which = 'income' then sum_amt else 0 end) as sum_income, sum(case when which = 'sale' then sum_amt else 0 end) as sum_sale, sum(case when which = 'income' then sum_amt else 0 end) - sum(case when which = 'sale' then sum_amt else 0 end) as differ_between from (select goods_id, sum(incomeqty) as sum_amt, 'income' as which from income where income.recdate betwwen :d1 and :d2 group by pr_k union all select goods_id, sum(salesqty), 'sale' from sales where sale.recdate between :d1 and :d2 group by goods_id) x group by goods_id

but how to add goods "name" and if clause("where goods_id=income.goods_id") in the query from the goods table?

UPDATE1

It seems that the request has already been created, but one problem: if there is no receipt and / or expense in the interval, then the DBGrid is empty, i.e. returns a null. And I want the GoodsList to always exist.

 select income.goods_id,dt2.goods_id,dt2.sum_income,dt2.sum_sales,dt2.differ_between from goods INNER JOIN (select goods_id, sum(case when which = 'income' then sum_amt else 0 end) as sum_income, sum(case when which = 'sale' then sum_amt else 0 end) as sum_sale, sum(case when which = 'income' then sum_amt else 0 end) - sum(case when which = 'sale' then sum_amt else 0 end) as differ_between from (select goods_id, sum(incomeqty) as sum_amt, 'income' as which from income where income.recdate between :d1 and :d2 group by goods_id union all select goods_id, sum(salesqty), 'sale' from sales where sale.recdate between :d1 and :d2 group by goods_id) dt1 group by goods_id )dt2 on goods.goods_id=dt2.goods_id

UPDATE2

It works correctly if there is a receipt and / or an expense in the interval, and if there is no arrival and expense in the interval, then the Goods_id, sum_income, sum_sale, differ_between fields are empty, i.e. returns a null. And I want the GoodsList to always exist.

 SELECT g.name, dt2.goods_id, dt2.sum_income, dt2.sum_sales, dt2.differ_between FROM goods g LEFT JOIN (SELECT goods_id, sum(sum_income) as sum_income, sum(sum_sale) as sum_sale, sum(sum_income - sum_sale) as differ_between from (select goods_id, sum(incomeqty) as sum_income, 0 as sum_sale, 1 as which from income where income.recdate between :d1 and :d2 group by goods_id union all select goods_id, 0, sum(salesqty), -1 from sales where sale.recdate between :d1 and :d2 group by goods_id) dt1 group by goods_id )dt2 on g.goods_id = dt2.goods_id

    1 answer 1

    try this

     SELECT g.name, g.goods_id, coalesce(dt2.sum_income,0), coalesce(dt2.sum_sales,0), coalesce(dt2.differ_between,0) FROM goods g LEFT JOIN (SELECT goods_id, sum(sum_income) as sum_income, sum(sum_sale) as sum_sale, sum(sum_income - sum_sale) as differ_between from (select goods_id, sum(incomeqty) as sum_income, 0 as sum_sale, 1 as which from income where income.recdate between :d1 and :d2 group by goods_id union all select goods_id, 0, sum(salesqty), -1 from sales where sale.recdate between :d1 and :d2 group by goods_id) dt1 group by goods_id ) dt2 on g.goods_id = dt2.goods_id
    • Saidolim writes "engine error: column unknown sum_amt". - Delphi159
    • @davidkoko corrected check, there were other fields there - Saidolim
    • one
      @davidkoko check the update. Now, if there is no product data, they will be there by 0am - Saidolim
    • one
      @davidkoko so it should not be, if you use g.name, g.goods_id, maybe you used dt2.goods_id - Saidolim
    • one
      everything works right. You are super. Thank you so much!!! - Delphi159