Question Query why not group ?

[dongtrien]

I have the following query, to group the records but not the group was thanks to you only help, the end when running excel file view:

SELECT TOP (100) PERCENT dbo.TABNHANVIENCA.CANV, dbo.TABHDBHCT.TENSP, dbo.TABDONVI.TENDVT, SUM(dbo.TABHDBHCT.SOLUONG) AS SL, 
SUM(dbo.TABHDBH.DOITRA) AS DTRA, dbo.TABHDBHCT.GIABAN AS DONGIA, CASE TABHDBH.DOITRA WHEN 0 THEN (TABHDBHCT.SOLUONG * TABHDBHCT.GIABAN)
ELSE (TABHDBHCT.SOLUONG - TABHDBH.DOITRA) * (TABHDBHCT.GIABAN) END AS THANHTIEN, dbo.TABHDBHCT.GIABAN * dbo.TABHDBHCT.GIAMGIA / 100 AS GGIA,
CASE TABHDBH.DOITRA WHEN 0 THEN (TABHDBHCT.SOLUONG * TABHDBHCT.GIABAN) - (dbo.TABHDBHCT.GIABAN * dbo.TABHDBHCT.GIAMGIA / 100) 
ELSE ((TABHDBHCT.SOLUONG - TABHDBH.DOITRA) * (TABHDBHCT.GIABAN)) - (dbo.TABHDBHCT.GIABAN * dbo.TABHDBHCT.GIAMGIA / 100) END AS TONGCONG, 
dbo.TABCUAHANG.TENCUAHANG
FROM dbo.TABHDBH INNER JOIN
dbo.TABHDBHCT ON dbo.TABHDBH.IDHDBH = dbo.TABHDBHCT.MAHDBH INNER JOIN
dbo.TABCUAHANG ON dbo.TABHDBHCT.MACUAHANG = dbo.TABCUAHANG.IDCUAHANG INNER JOIN
dbo.TABDONVI ON dbo.TABHDBHCT.MADVT = dbo.TABDONVI.IDDVT INNER JOIN
dbo.TABNHANVIENCA ON dbo.TABHDBH.MACA = dbo.TABNHANVIENCA.IDCANV
WHERE (dbo.TABHDBH.NGAY BETWEEN '07/01/2017' AND '08/18/2017') AND (dbo.TABCUAHANG.TENCUAHANG = N'C?a hàng nha trang') AND 
(dbo.TABHDBH.TTTHANHTOAN >= 0) AND (dbo.TABHDBH.TTTHANHTOAN <= 2)
GROUP BY dbo.TABNHANVIENCA.CANV, dbo.TABHDBHCT.TENSP, dbo.TABDONVI.TENDVT, dbo.TABHDBHCT.SOLUONG, dbo.TABHDBH.DOITRA, dbo.TABHDBHCT.GIABAN, 
dbo.TABHDBHCT.GIAMGIA, dbo.TABCUAHANG.TENCUAHANG
ORDER BY dbo.TABNHANVIENCA.CANV DESC, dbo.TABHDBHCT.TENSP[/code]

http://www.mediafire.com/file/ezwbitr7a2gjzve/sSQLNhom.xls

[pty]

As a SQL contractor who comes across computer-generated messes like this rather frequently, I'd like to make a suggestion. Break this up into manageable chunks.

Here's your query with syntax highlighting:

Attempting to do the case statements alongside aggregate functions with a load of joins and criteria is complex, but you can make it easier by creating an intermediate view that just does the select and case statements. Then, from that view, try to add your where clause. If that's all fine, now try adding the aggregates (sum and group by).

[dongtrien]

I tried to edit SQL but It could not. You see the excel file, it does not group results and sums in SL columns and DTRA columns, if the number of excel files is: 9,10 and 14,15. Do you ask any more ?