Sum subquery MS SQL database

Goran Bibic

2y801SQL

Need to sum this column

(t.kolicina)*(sum(s.kolicina)) as 'Kol potr repromat'

When try

sum((t.kolicina)*(sum(s.kolicina))) as 'Kol potr repromat'

Have this error

Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

My code

select t.naziv as 'Naziv' ,
  (t.kolicina)*(sum(s.kolicina)) as 'Kol potr repromat'
  from dbo.normativi_roba t
  full join
normativi_lista k
ON t.id_fakture =k.id
full join
mp_racun_roba s
ON k.naziv=s.roba
where roba=(select naziv from dbo.roba_usluge where podgrupa_artikala='HRANA' and roba_usluge.naziv=s.roba)
group by t.naziv, t.jedinica_mjere, t.kolicina

1 Reply

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

Tahir Ansari2y ago

Accepted answer

SELECT
    naziv AS 'Naziv',
    SUM(Kol_potr_repromat) AS 'Sum_Kol_potr_repromat'
FROM (
    SELECT
        t.naziv,
        t.kolicina * SUM(s.kolicina) AS 'Kol_potr_repromat'
    FROM
        dbo.normativi_roba t
    FULL JOIN
        normativi_lista k ON t.id_fakture = k.id
    FULL JOIN
        mp_racun_roba s ON k.naziv = s.roba
    WHERE
        s.roba IN (SELECT naziv FROM dbo.roba_usluge WHERE podgrupa_artikala = 'HRANA')
    GROUP BY
        t.naziv, t.jedinica_mjere, t.kolicina
) AS SubqueryAlias
GROUP BY
    naziv;