SQL Create Multiple Column from Multiple Tables

Question

SQL SERVER 2017

Some help

Error is line 4

I need first column Naziv artikla (Line 1)

second column Prodata Kolicina (Line 2)

Third column Nabaljena kolicina (Line 4) some error

Fourth column difference Line4 - Line 2 as Ukupno

SELECT roba as 'Naziv artikla',
SUM (cast(kolicina as float)) as 'Prodata kolicina'
from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables
INNER JOIN mp_ulazni_racun_ostalo_roba ON alltables.kolicina=mp_ulazni_racun_ostalo_roba.kolicina as 'Nabavljena kolicina'
--SUM COLUMN 'Nabavljena kolicina' - 'Prodata kolicina' //result column
group by roba

Packiaraj Santhiyagu · Answer

Hi Goran, sorry, I missed one . SELECT roba as [Naziv artikla], SUM ( cast (kolicina as float )) as [Prodata kolicina] from ( select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables INNER JOIN mp_ulazni_racun_ostalo_roba ON alltables.kolicina=mp_ulazni_racun_ostalo_roba.kolicina --SUM COLUMN [Nabavljena kolicina] - [Prodata kolicina] //result column group by roba try now.

Goran Bibic · Answer

Packiaraj Santhiyagu Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'as' .

Nitin Sontakke · Answer

If I may jump in, the kolicina column is in two tables, one AllTables cte and in mp_ulazni_racun_ostalo_roba table as well. So on line 8, you will need specify kolicina column from which table should be used.

Packiaraj Santhiyagu · Answer

Hi Goran, use below changed query it will work SELECT roba as [Naziv artikla], SUM ( cast (kolicina as float )) as [Prodata kolicina] from ( select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables INNER JOIN mp_ulazni_racun_ostalo_roba ON alltables.kolicina=mp_ulazni_racun_ostalo_roba.kolicina as [Nabavljena kolicina] --SUM COLUMN [Nabavljena kolicina] - [Prodata kolicina] //result column group by roba

Goran Bibic · Answer

Regards James Axsom Some help Msg 209, Level 16, State 1, Line 8 Ambiguous column name 'kolicina'. Line 8 and I need in line one more table mp_ulazni_racun_roba WITH AllTables (roba, kolicina) AS ( SELECT roba,kolicina FROM MP_RACUN_ROBA UNION ALL SELECT roba,kolicina FROM mp_faktura_roba ) SELECT roba AS 'Naziv artikla' , SUM (cast(kolicina AS float )) AS 'Prodata kolicina' FROM AllTables AT INNER JOIN mp_ulazni_racun_ostalo_roba MPU ON AT.kolicina=MPU.kolicina GROUP BY roba

James Axsom · Answer

Hi Goran, I can't guarantee you this will work because I don't have access to your database, but use a common table expression to get access to the columns you need like this. WITH AllTables (ROBA, KOLICINA) AS { SELECT ROBA, KOLICINA FROM MP_RACUN_ROBA UNION ALL SELECT ROBA, KOLICINA FROM MP_FAKTURA_RABO } SELECT ROBA AS 'Naziv artikla' , SUM ( cast (KOLICINA AS float )) AS 'Prodata kolicina' FROM AllTables AT INNER JOIN mp_ulazni_racun_ostalo_roba MPU ON AT .kolicina=MPU.kolicina GROUP BY ROBA; Hope this gives ideas or inspiration for a solution.

Shraddha Patel · Answer

SELECT fn.PropertyValue FirstName, ln.PropertyValue LastName, etc... From UserProfile fN Join UserProfile lN On fN.PropertyName = 'first name' And ln.PropertyName = 'last name' And fn.user = ln.user Join UserProfile eM On fN.PropertyName = 'first name' And eM.PropertyName = 'email' And eM.user = fn.user (