Select from multiple tables and make difference

Goran Bibic

8y911SQL

Line 2 or Line 3 alone working

I need to create column from multiple tables and sum...

Some help?

select naziv as 'Naziv artikla',
SUM (cast(kolicina as float)) as 'Nabavka' from (select naziv, kolicina from ulazni_racun_roba_roba union all select naziv, kolicina from mp_ulazni_racun_ostalo_roba) as alltables
SUM (cast(kolicina as float)) as 'Prodaja' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables
group by naziv

I need result column:

1) Naziv artikla

2) Nabavka

3) Prodaja

4) Difference (Nabavka-Prodaja)

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya8y ago
Accepted answer
declare @ulazni_racun_roba_roba table (naziv varchar(32),kolicina varchar(32))
declare @mp_ulazni_racun_ostalo_roba table (naziv varchar(32),kolicina varchar(32))
declare @mp_racun_roba table (roba varchar(32),kolicina varchar(32))
declare @mp_faktura_roba table (roba varchar(32),kolicina varchar(32))
--insert data in all table and then try
select naziv AS 'Naziv artikla',sum(Nabavka) As 'Nabavka',sum(Prodaja) As 'Prodaja', (sum(Nabavka)-sum(Prodaja)) As 'Difference'
from (
select naziv , SUM (cast(kolicina as float)) as Nabavka,SUM (cast(kolicina as float)) as Prodaja
from (
select naziv, kolicina from @ulazni_racun_roba_roba
union all
select naziv, kolicina from @mp_ulazni_racun_ostalo_roba
) as alltables
group by naziv
union all
select roba as naziv, SUM (cast(kolicina as float)) as Nabavka ,SUM (cast(kolicina as float)) as Prodaja
from (
--i think roba is different collumn but i use (roba and naziv is same collumn and both are pk)
select roba , kolicina from @mp_racun_roba
union all
select roba , kolicina from @mp_faktura_roba
) as alltables
group by roba
) final
group by naziv
Goran Bibic8y ago
Example
Nitin Sontakke8y ago
As I keep saying to everybody, please BREAK the problem in pieces. DO NOT swollow the big chunks.
Nitin Sontakke8y ago
Goran,

Please read carefully and answer questions properly.

You seem to be selecting sums from two completely different tables. Is there any relationship between these two tables? If yes, what is the joining key? If there is relationship then only it can be done. No relationship, cannot be done.

This is logical.

Please give sample 3-4 rows from each table and show us if they related then how.