• Select from multiple tables and make difference MS SQL

Select from multiple tables and make difference MS SQL

  • 1.5K Views
  • Last Post 03 September 2018
  • Topic Is Solved
Stylus STYLUS posted this 27 August 2018

I need to create column from multiple tables and sum...after create diffence column. 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
SUM COLUMN [Nabavljena kolicina] - [Prodata kolicina] //result column difference between    
group by naziv

   

 I need result column:

1) Naziv artikla 2) Nabavka 3) Prodaja 4) Difference (Nabavka-Prodaja)

sql
Order By: Standard | Newest | Votes
lucy posted this 27 August 2018

Hi, You can try the query below

select naziv, Nabavka, Prodaja, Nabavka-Prodaja as x from
(select naziv, sum(kolicina) as Nabavka from
(
select naziv, kolicina from ulazni_racun_roba_roba
union all
select naziv, kolicina from mp_ulazni_racun_ostalo_roba
)
group by naziv

union all

select naziv, sum(kolicina) as Prodaja from
(
select roba as naziv, kolicina from mp_racun_roba
union all
select roba as naziv, kolicina from mp_faktura_roba
)
group by naziv
)

Can you translate all your columns into English language to help me understand what you need.

Stylus STYLUS posted this 27 August 2018

1) Naziv artikla (Article name)   (naziv(name))

2) Nabavka (procurement)

3) Prodaja (Sale)

4) Difference (procurement-sale)

Stylus STYLUS posted this 28 August 2018

I think is now ok translated

select name, procurement, Sale, procurement-Sale as x from
(select name, sum(quantity) as procurement from
(
select name, quantity from ulazni_racun_roba_roba
union all
select name, quantity from mp_ulazni_racun_ostalo_roba
)
group by name

union all

select name, sum(quantity) as Sale from
(
select roba as name, quantity from mp_racun_roba
union all
select roba as name, quantity from mp_faktura_roba
)
group by name
)

Error is

Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'group'.
Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'group'.

lucy posted this 28 August 2018

You can try the snippet below

select x1.naziv, x1.Prodaja, x2.Nabavka, x2.Nabavka-x1.Prodaja as x
from
(
    select naziv, sum(kolicina) as Prodaja from
    (
    select roba as naziv, convert(float, kolicina) as kolicina from mp_racun_roba
    union all
    select roba as naziv, convert(float, kolicina) as kolicina from mp_faktura_roba
    ) a
    group by naziv
) x1
left join
(
    select naziv, sum(kolicina) as Nabavka from
    (
    select naziv, convert(float, kolicina) as kolicina from ulazni_racun_roba_roba
    union all
    select naziv, convert(float, kolicina) as kolicina from mp_ulazni_racun_ostalo_roba
    ) b
    group by naziv
) x2
on x1.naziv = x2.naziv
where x2.naziv is not null
  • Liked by
  • Stylus STYLUS
Stylus STYLUS posted this 28 August 2018

That is ok...thank you

Stylus STYLUS posted this 02 September 2018

I have one problem with this code....I just have articles if I have in both tables....I need all articles from both tables... If I got goods and I did not sell it, I do not have it on that report...some help?

Stylus STYLUS posted this 02 September 2018

Solved ....just remove this line

where x2.naziv is not null
Stylus STYLUS posted this 02 September 2018

I need IF IS null then 0 some help?

admin posted this 03 September 2018

You can use isnull('your column', 0) as column

For Example

select isnull('kolicina', 0) as kolicina from ulazni_racun_roba_roba
  • Liked by
  • Stylus STYLUS
Stylus STYLUS posted this 03 September 2018

Solved. Thank you

Search
Popular Tags
This Weeks High Earners
Hot Topics
Close