query group and Sum number has got Conditional ?

Dong Lam Trien

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]

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dong Lam Trien9y ago
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
Manas Mohapatra9y ago
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;

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