none
query group and the average value ? RRS feed

  • 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.

    Monday, March 6, 2017 8:03 AM

Answers

  • 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 MSDNFSF@microsoft.com.

    • Marked as answer by lamtriendong Friday, March 10, 2017 1:31 AM
    • Unmarked as answer by lamtriendong Sunday, March 12, 2017 9:16 AM
    • Marked as answer by lamtriendong Sunday, March 12, 2017 9:35 AM
    Tuesday, March 7, 2017 8:33 AM

All replies

  • [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)

    Monday, March 6, 2017 11:22 AM
  • Thank you for your answer
    Tuesday, March 7, 2017 6:21 AM
  • 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 MSDNFSF@microsoft.com.

    • Marked as answer by lamtriendong Friday, March 10, 2017 1:31 AM
    • Unmarked as answer by lamtriendong Sunday, March 12, 2017 9:16 AM
    • Marked as answer by lamtriendong Sunday, March 12, 2017 9:35 AM
    Tuesday, March 7, 2017 8:33 AM