Group from Join or Union Two Query

Question

Need to sum 'Kol prod danas' column and group by 'Prodano danas' column

 SELECT
    c.sifra AS 'Šifra RM',
    c.naziv AS 'Naziv RM',
    SUM(c.kolicina) AS 'Kol RM',
   -- d.sifra AS success1,
    d.roba AS 'Prodano danas'	,
    SUM(d.kolicina) AS 'Kol prod danas'
FROM
    popis_repromaterijal_roba AS c 
    JOIN mp_racun_roba AS d  
	
	ON D.id=C.id
	GROUP BY
    c.sifra,
    c.naziv,
    d.roba

My result

Šifra RM	Naziv RM	Kol RM	Prodano danas	Kol prod danas
1	MESO MLJEVENO	4.1060	HLJEB BIJELI POSAVAC 	1.0000
2	PICA SUNKA	6.2060	HLJEB BIJELI POSAVAC 	1.0000
3	PILETINA	4.1220	HLJEB BIJELI POSAVAC 	2.0000
4	SIR GAUDA	2.9680	PIZZA 	1.0000
5	KROMPIR	5.0000	BUREK VRHNJE 	1.0000
6	SIR SVJEZI	5.0000	HLJEB BIJELI POSAVAC 	-2.0000
7	BRASNO	200.0000	PIZZA 	-1.0000

MY result need to be

Šifra RM	Naziv RM	Kol RM	Prodano danas	Kol prod danas
1	MESO MLJEVENO	4.1060	HLJEB BIJELI POSAVAC 	2.0000
2	PICA SUNKA	6.2060	    BUREK VRHNJE 	1.0000
3	PILETINA	4.1220	    
4	SIR GAUDA	2.9680	    
5	KROMPIR	5.0000	
6	SIR SVJEZI	5.0000	
7	BRASNO	200.0000

Goran Bibic · Answer

https://www.c-sharpcorner.com/members/tahir-ansari4 Error is SELECT c.sifra AS 'Šifra RM', c.naziv AS 'Naziv RM', SUM(c.kolicina) AS 'Kol RM', '' AS 'Prodano danas', '' AS 'Kol prod danas' FROM popis_repromaterijal_roba AS c GROUP BY c.sifra, c.naziv UNION SELECT '', '', '', d.roba, SUM(d.kolicina) FROM mp_racun_roba AS d GROUP BY d.roba Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

Tahir Ansari · Answer

Hi Goran Bibic please try this query: SELECT c.sifra AS 'Šifra RM', c.naziv AS 'Naziv RM', SUM(c.kolicina) AS 'Kol RM', '' AS 'Prodano danas', '' AS 'Kol prod danas' FROM popis_repromaterijal_roba AS c GROUP BY c.sifra, c.naziv UNION SELECT '', '', '', d.roba, SUM(d.kolicina) FROM mp_racun_roba AS d GROUP BY d.roba;

Tahir Ansari · Answer

SELECT c.sifra AS 'Šifra RM', c.naziv AS 'Naziv RM', SUM(c.kolicina) AS 'Kol RM', NULL AS 'Prodano danas', NULL AS 'Kol prod danas' FROM popis_repromaterijal_roba AS c GROUP BY c.sifra, c.naziv UNION SELECT NULL AS 'Šifra RM', NULL AS 'Naziv RM', NULL AS 'Kol RM', d.roba AS 'Prodano danas', SUM(d.kolicina) AS 'Kol prod danas' FROM mp_racun_roba AS d GROUP BY d.roba; try this one also if above query not work: SELECT c.sifra AS 'Šifra RM', c.naziv AS 'Naziv RM', c.kolicina AS 'Kol RM', d.roba AS 'Prodano danas', d.kolicina AS 'Kol prod danas' FROM popis_repromaterijal_roba AS c JOIN mp_racun_roba AS d ON d.id = c.id UNION SELECT sifra AS 'Šifra RM', naziv AS 'Naziv RM', kolicina AS 'Kol RM', NULL AS 'Prodano danas', NULL AS 'Kol prod danas' FROM popis_repromaterijal_roba WHERE sifra NOT IN (SELECT DISTINCT id FROM mp_racun_roba);

Mohammad Hussain · Answer

SELECT c.sifra AS 'Šifra RM', c.naziv AS 'Naziv RM', SUM(c.kolicina) AS 'Kol RM', d.roba AS 'Prodano danas', SUM(d.kolicina) AS 'Kol prod danas' FROM popis_repromaterijal_roba AS c LEFT JOIN mp_racun_roba AS d ON c.id = d.id GROUP BY c.sifra, c.naziv, d.roba;

Goran Bibic · Answer

No need emtpy places, please repair Thank you

Goran Bibic · Answer

Nothing happend Mohammad Hussain All is same Šifra RM Naziv RM Kol RM Prodano danas Kol prod danas 1 MESO MLJEVENO 4.1060 HLJEB BIJELI POSAVAC 1.0000 2 PICA SUNKA 6.2060 HLJEB BIJELI POSAVAC 1.0000 3 PILETINA 4.1220 HLJEB BIJELI POSAVAC 2.0000 4 SIR GAUDA 2.9680 PIZZA 1.0000 5 KROMPIR 5.0000 BUREK VRHNJE 1.0000 6 SIR SVJEZI 5.0000 HLJEB BIJELI POSAVAC -2.0000 7 BRASNO 200.0000 PIZZA -1.0000