query group and Sum number has got Conditional ?

Question

I have the following SQL Server query: requirements SUM and GROUP column SOLUONG AS SL  if TRAHANG = true then bring positive column opposite quantity quantity column value is negative no team and no columns SL sum is if I would give TRAHANG column in the SELECT statement error. Can you help me ? [CODE] //Query warning eror SELECT TABHDBHCT.MAVACH_SP TABHDBHCT.TENSP SUM(TABHDBHCT.SOLUONG)*CASE WHEN [TRAHANG] = 0 THEN 1 ELSE -1 END AS SL FROM TABHDBHCT INNER JOIN TABHDBH ON TABHDBHCT.MASOPHIEU = TABHDBH.IDSOPHIEU WHERE (TABHDBH.TTTHANHTOAN = 1 OR TABHDBH.TTTHANHTOAN = 2) AND (TABHDBH.NGAY BETWEEN '11/01/2016' AND '11/30/2016') GROUP BY TABHDBHCT.MAVACH_SP TABHDBHCT.TENSP ORDER BY TABHDBHCT.TENSP; [/CODE]

Manas Mohapatra · Answer

You have to add that column in group by like below: SELECT TABHDBHCT.MAVACH_SP TABHDBHCT.TENSP TRAHANG SUM (TABHDBHCT.SOLUONG) * CASE WHEN [ TRAHANG ] = 0 THEN 1 ELSE -1 END AS SL FROM TABHDBHCT INNER JOIN TABHDBH ON TABHDBHCT.MASOPHIEU = TABHDBH.IDSOPHIEU WHERE (TABHDBH.TTTHANHTOAN = 1 OR TABHDBH.TTTHANHTOAN = 2) AND (TABHDBH.NGAY BETWEEN '11/01/2016' AND '11/30/2016' ) GROUP BY TABHDBHCT.MAVACH_SP TABHDBHCT.TENSP TRAHANG ORDER BY TABHDBHCT.TENSP;

Dong Lam Trien · Answer

According to your instructions It's not Group column SL total the result will be: 4567954 coffe nestle False 6 4654126 Cocacola False 10 4654126 Cocacola True -6 5647954 ana False 10 456789 milk False 12 456789 milk True -4 1234568 gcafe False 7 1234568 gcafe True -2 6492455 pesi False 11 6492455 pesi True -5 [CODE] SELECT TOP TABHDBHCT.MAVACH_SP dbo.TABHDBHCT.TENSP TABHDBHCT.TRAHANG SUM(dbo.TABHDBHCT.SOLUONG) * CASE WHEN [TRAHANG] = 0 THEN 1 ELSE - 1 END AS SL FROM TABHDBHCT INNER JOIN TABHDBH ON TABHDBHCT.MASOPHIEU = TABHDBH.IDSOPHIEU WHERE (TABHDBH.TTTHANHTOAN = 1 OR TABHDBH.TTTHANHTOAN = 2) AND (TABHDBH.NGAY BETWEEN '11/01/2016' AND '11/30/2016') GROUP BY TABHDBHCT.MAVACH_SP TABHDBHCT.TENSP TABHDBHCT.TRAHANG ORDER BY TABHDBHCT.TENSP [/CODE] Here are the results I need: 4567954 coffe nestle 6 4654126 Cocacola 4 5647954 ana 10 456789 milk 8 1234568 gcafe 5 6492455 pesi 6