Select from multiple tables and make difference MS SQL

  • 121 Views
  • Last Post 3 weeks ago
  • Topic Is Solved
Stylus STYLUS posted this 4 weeks ago

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)

Order By: Standard | Newest | Votes
lucy posted this 4 weeks ago

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 4 weeks ago

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

2) Nabavka (procurement)

3) Prodaja (Sale)

4) Difference (procurement-sale)

Stylus STYLUS posted this 4 weeks ago

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 4 weeks ago

Can you send to me your database: lucyhynh@gmail.com

Stylus STYLUS posted this 4 weeks ago

Sent

lucy posted this 4 weeks ago

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 4 weeks ago

That is ok...thank you

Stylus STYLUS posted this 3 weeks ago

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 3 weeks ago

Solved ....just remove this line

where x2.naziv is not null
Stylus STYLUS posted this 3 weeks ago

I need IF IS null then 0 some help?

admin posted this 3 weeks ago

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 3 weeks ago

Solved. Thank you

Close