query group and the average value ?

Question

I create query group and the average value of the columns [DONGIA] when I run it does not result

unexpected results (column [DONGIA] results is false while query qryResults)

STT	MANHOM	MAVACH_SC	TENSP	DVT	DONGIA	GIAVON	TON	GIATRI	VONTRI
1	1	1234568	gcafe	plate	7000	10000	-15	-105000	-150000
1	1	1234568	gcafe	plate	9800	10000	-8	-78400	-80000
1	1	1234568	gcafe	plate	10000	10000	-15	-150000	-150000
1	1	1234568	gcafe	plate	25000	10000	-13	-325000	-130000
1	1	1234568	gcafe	plate	65000	10000	57	3705000	570000
1	1	1234568	gcafe	plate	95000	10000	-7	-665000	-70000
2	2	456789	cashew	plate	20000	35000	-15	-300000	-525000
2	2	456789	cashew	plate	35000	35000	-42	-1470000	-1470000
2	2	456789	cashew	plate	55000	35000	40	2200000	1400000
2	2	456789	cashew	plate	85000	35000	-20	-1700000	-700000
2	2	456789	cashew	plate	350000	35000	-43	-15050000	-1505000
3	1	4567954	nestle	plate	15000	20000	24	360000	480000
3	1	4567954	nestle	plate	20000	20000	15	300000	300000
3	1	4567954	nestle	plate	25000	20000	39	975000	780000
4	2	4654126	Cocacola	plate	10000	40000	-1	-10000	-40000
4	2	4654126	Cocacola	plate	35000	40000	29	1015000	1160000
4	2	4654126	Cocacola	plate	65000	40000	-6	-390000	-240000
4	2	4654126	Cocacola	plate	150000	40000	-27	-4050000	-1080000
5	1	5647954	apple	plate	20000	30000	19	380000	570000
5	1	5647954	apple	plate	45000	30000	49	2205000	1470000
5	1	5647954	apple	plate	75000	30000	4	300000	120000
6	3	6492455	pesi	argue	9000	23000	-4	-36000	-92000
6	3	6492455	pesi	argue	15000	23000	22	330000	506000
6	3	6492455	pesi	argue	23000	23000	-19	-437000	-437000

I need results when queries (result is true)

STT	MANHOM	MAVACH_SC	TENSP	DVT	DONGIA	GIAVON	TON	GIATRI	VONTRI
1	1	1234568	gcafe	plate	35000	10000	-7	-665000	-70000
2	2	456789	cashew	plate	109000	35000	-15	-300000	-525000
3	1	4567954	nestle	plate	20000	20000	39	975000	780000
4	2	4654126	Cocacola	plate	65000	40000	-1	-10000	-40000
5	1	5647954	apple	plate	46666	30000	4	300000	120000
6	3	6492455	pesi	argue	15666	23000	-4	-36000	-92000

My query is: 

SELECT (SELECT COUNT(*)
     FROM 
        (SELECT DISTINCT MAVACH_SC
         FROM TABPHIEUKHOCTTMP) AS T2
         WHERE T2.MAVACH_SC <= T1.MAVACH_SC) AS STT, T1.MANHOM, T1.MAVACH_SC, T1.TENSP, T1.DVT, Avg(T1.DONGIA) AS AvgDONGIA, T1.GIAVON, Sum([SLNHAP]-([SLXUAT]+[SLBAN])) AS TON, [DONGIA]*[TON] AS GIATRI, [GIAVON]*[TON] AS VONTRI
FROM TABPHIEUKHOCTTMP AS T1
GROUP BY T1.MANHOM, T1.MAVACH_SC, T1.TENSP, T1.DVT, T1.GIAVON
ORDER BY T1.MAVACH_SC;

query Error:

You tried to execute a query that does not include the specified expression[DONGIA]*[TON] as part of a aggregate function.

Answer 1

[DONGIA]*[TON] AS GIATRI doesn't use an aggregate function such as Sum or Avg.

The same holds for [GIAVON]*[TON] AS VONTRI.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thank you for your answer

Answer 3

Hi,

If the suggestion from Hans work for you, I would suggest you mark the suggestion as answer.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].