use tempdb;
go

if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01

CREATE TABLE #tab01 (
NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab01 (NAME, AMOUNT, PRICE)
VALUES
('МОЛОКО', 10, 80.00),
('МОЛОКО', 5, 85.00),
('СМЕТАНА', 10, 100.00),
('СМЕТАНА', 5, 105.00),
('КЕФИР', 10, 90.00),
('КЕФИР', 5, 95.00)

SELECT NAME, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT*PRICE) AS SUMA FROM #tab01 GROUP BY NAME 

if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02

CREATE TABLE #tab02 (
NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab02 (NAME, AMOUNT, PRICE)
VALUES
('МОЛОКО', 5, 110.00),
('МОЛОКО', 5, 110.00),
('СМЕТАНА', 5, 120.00),
('СМЕТАНА', 5, 120.00),
('КЕФИР', 5, 100.00),
('КЕФИР', 5, 100.00)

SELECT NAME, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT*PRICE) AS SUMA FROM #tab02 GROUP BY NAME 

строка: NAME, #tab01.AMOUNT, #tab01.SUMA, #tab02.AMOUNT, #tab02.SUMA, #tab01.AMOUNT-#tab02.AMOUNT

строка: КЕФИР 15 1375,00 10 1000, 5

SELECT 
 T1.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,(SUM(T1.AMOUNT) - SUM(T2.AMOUNT)) AS BALANCE
 FROM #tab01 T1 
  LEFT OUTER JOIN #tab02 AS T2 ON(T2.NAME = T1.NAME)
 GROUP BY T1.NAME

USE tempdb;
GO

if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01

CREATE TABLE #tab01 (
ID int 
,NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab01 (ID, NAME, AMOUNT, PRICE)
VALUES
(1,'МОЛОКО', 10, 80.00),
(1,'МОЛОКО', 5, 85.00),
(2,'СМЕТАНА', 10, 100.00),
(2,'СМЕТАНА', 5, 105.00),
(3,'КЕФИР', 10, 90.00),
(3,'КЕФИР', 5, 95.00)


if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02

CREATE TABLE #tab02 (
ID int 
,NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab02 (ID, NAME, AMOUNT, PRICE)
VALUES
(1,'МОЛОКО', 5, 110.00),
(1,'МОЛОКО', 5, 110.00),
(2,'СМЕТАНА', 5, 120.00),
(2,'СМЕТАНА', 5, 120.00),
(3,'КЕФИР', 5, 100.00),
(3,'КЕФИР', 5, 100.00)


SELECT DISTINCT
  T1.ID
 ,T1.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,(SUM(T1.AMOUNT) - SUM(T2.AMOUNT)) AS BALANCE
 FROM #tab01 T1 
  LEFT OUTER JOIN #tab02 AS T2 ON(T2.NAME = T1.NAME)
 GROUP BY T1.ID, T1.NAME

--словарь продуктов
if OBJECT_ID('#voc01','u') IS NOT NULL DROP TABLE #voc01
CREATE TABLE #voc01 (ID int IDENTITY(1,1)  ,NAME nvarchar(100));
INSERT INTO #voc01 (NAME) VALUES ('МОЛОКО'),('СМЕТАНА'),('КЕФИР')
--таблица покупок
if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01
CREATE TABLE #tab01 (ID_VOC int, AMOUNT int, PRICE float);
INSERT INTO #tab01 (ID_VOC, AMOUNT, PRICE)
VALUES (1, 10, 80.00),(1, 5, 85.00),(2, 10, 100.00),(2, 5, 105.00),(3, 10, 90.00),(3, 5, 95.00),(3, 2, 96.00)
--таблица продаж
if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02
CREATE TABLE #tab02 (ID_VOC int ,NAME nvarchar(100) ,AMOUNT int ,PRICE float);
INSERT INTO #tab02 (ID_VOC, AMOUNT, PRICE)
VALUES (1, 5, 110.00), (1, 5, 110.00), (2, 5, 120.00), (2, 5, 120.00),  (3, 5, 100.00), (3, 5, 100.00)
-- тот набор данных, который хотелось получить
 SELECT 
  V.ID
 ,V.NAME
 ,(SELECT SUM(T1.AMOUNT) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID) AS AMOUNT_ARR
 ,(SELECT SUM(T1.AMOUNT*T1.PRICE) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID)  AS SUMA_ARR
 ,(SELECT SUM(T2.AMOUNT) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID) AS AMOUNT_EX
 ,(SELECT SUM(T2.AMOUNT*T2.PRICE) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID) AS SUMA_EX
 ,((SELECT SUM(T1.AMOUNT) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID) - (SELECT SUM(T2.AMOUNT) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID)) AS BALANCE
 FROM #voc01 V 
 GROUP BY V.ID, V.NAME
 ORDER BY V.ID

 SELECT 
  V.ID
 ,V.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,SUM(T1.AMOUNT)-(SUM(T2.AMOUNT)) AS BALANCE
 FROM 
 #voc01 V, 
 #tab01 AS T1,
 #tab02 AS T2 
 WHERE 
 T1.ID_VOC = V.ID
 AND
 T2.ID_VOC = V.ID
 GROUP BY V.ID, V.NAME
 ORDER BY V.ID

SELECT t.NAME, t.AMOUNT AS A1, t.SUMA AS S1, v.AMOUNT AS A2, v.SUMA AS S2, t.AMOUNT - ISNULL(v.AMOUNT, 0) AS ADIFF
FROM (
	SELECT v.NAME, SUM(t.AMOUNT) AS AMOUNT, SUM(t.AMOUNT*t.PRICE) AS SUMA
	FROM #tab01 t JOIN #voc01 v ON t.ID_VOC = v.ID
	GROUP BY v.NAME
) t
LEFT JOIN
(
	SELECT v.NAME, SUM(t.AMOUNT) AS AMOUNT, SUM(t.AMOUNT*t.PRICE) AS SUMA
	FROM #tab02 t JOIN #voc01 v ON t.ID_VOC = v.ID
	GROUP BY v.NAME
) v ON t.NAME = v.NAME

SELECT V.ID, V.NAME, T1.AMOUNT_ARR, T1.SUMA_ARR, T2.AMOUNT_EX, T2.SUMA_EX, T1.AMOUNT_ARR - ISNULL(T2.AMOUNT_EX,0) AS BALANCE
 FROM
 (
 SELECT ID_VOC, SUM(AMOUNT) AS AMOUNT_ARR, SUM(AMOUNT*PRICE) AS SUMA_ARR
 FROM #tab01
 GROUP BY ID_VOC
) AS T1,
 (
 SELECT ID_VOC, SUM(AMOUNT) AS AMOUNT_EX, SUM(AMOUNT*PRICE) AS SUMA_EX
 FROM #tab02
 GROUP BY ID_VOC
) AS T2,
#voc01 V
WHERE T1.ID_VOC = V.ID AND T2.ID_VOC = V.ID