SQL Create Multiple Column from Multiple Tables

Goran Bibic

8y1.1kSQL

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

7 Replies

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

Shraddha Patel7y ago
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
(
Packiaraj Santhiyagu8y ago
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 Bibic8y ago
Packiaraj Santhiyagu
Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'as'.
Nitin Sontakke8y ago
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 Santhiyagu8y ago
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 Bibic8y ago
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 Axsom8y ago
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.

SQL Create Multiple Column from Multiple Tables

James Axsom