CODEWITHSUNDEEP
sqlfirebird
Delphi159
Delphi159

Reputation: 180

add column in query on Firebird

On the Firebird 2.5 server DB has tables: goods, income, sale.
Goods table has columns: goods_id, name
Income: Income_id,Goods_id,incomeqty(quantity),recdate
Sale: sales_id,income_id,Goods_id,saleqty,recdate

I have query that selects incomes, sales and differences between them in date intervals and it works correctly:

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 I want additionally select goods names from goods table. How include "name" column and if clause("where goods_id=income.goods_id" ) in this query?

Upvotes: 0

Views: 688

Answers (1)

Livius
Livius

Reputation: 956

Simple add join 

select x.goods_id, G.name,
  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 
    INNER JOIN GOODS G ON G.goods_id=x.goods_id
group by x.goods_id, G.name

but i do not know how your posted query work "select income.goods_id" if income is not included?

Modified version to support your comment

select x.goods_id, G.name,
  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 
    GOODS G LEFT JOIN
    (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 ON G.goods_id=x.goods_id
group by x.goods_id, G.name

but i suppose better then will be changing order of fields in "group by" and "select" to:

select G.name, x.goods_id ...
....
group by G.name, x.goods_id ...

Upvotes: 1

Related Questions