Error query sql server group and sum into one line ?

You can combine the SUM and GROUP BY clauses into a single line by using the OVER clause with the PARTITION BY statement. Here's the refactored query with the SUM and GROUP BY combined into one line:

SELECT DISTINCT
    TABHDBHCT.MACUAHANG,
    TABHDBHCT.MAHDBH,
    TABHDBH.MABAN,
    TABHDBH.SOPHIEU,
    SUM(TABHDBHCT.SOLUONG * TABHDBHCT.GIABAN) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TIENHANG,
    SUM(TABHDBHCT.SOLUONG * CASE WHEN TABHDBHCT.MANHOM = 14 THEN TABHDBHCT.GIABAN ELSE 0 END) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TTGIO,
    TABHDBH.GIAMPGIO,
    SUM(TABHDBH.GIAMTGIO) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TGIAMTGIO,
    SUM(TABHDBHCT.SOLUONG * TABHDBHCT.GIAMTIENCK) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TGIAMTIENCK,
    SUM(TABHDBHCT.SOLUONG * CASE WHEN TABHDBHCT.MANHOM <> 14 THEN (TABHDBHCT.GIABAN - TABHDBHCT.GIAMTIENCK) ELSE 0 END) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TTDU,
    TABHDBH.GIAMPDU,
    SUM(TABHDBH.GIAMTDU) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TGIAMTDU,
    SUM(TABHDBHCT.SOLUONG * (TABHDBHCT.GIABAN - TABHDBHCT.GIAMTIENCK)) OVER (PARTITION BY TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU) AS TONGTIEN
FROM
    dbo.TABHDBHCT
INNER JOIN dbo.TABHDBH ON TABHDBHCT.MAHDBH = TABHDBH.IDHDBH
WHERE
    TABHDBHCT.MAHDBH = 62
    AND TABHDBHCT.MACUAHANG = 1
    AND TABHDBHCT.DEL = 0
    AND TABHDBH.DEL = 0
ORDER BY
    TABHDBHCT.MAHDBH;

In this refactored query, the SUM function is used with the OVER clause, which partitions the data based on the specified columns (TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU). The OVER clause allows you to calculate the aggregate functions within each partition. This eliminates the need for a separate GROUP BY clause.

Based on the provided SQL query, it seems that the intention is to sum and group the data into one line. However, there might be some factors preventing the desired result. Here are a few things to consider:

1. DISTINCT: The query starts with a DISTINCT keyword, which ensures that the result set only contains distinct rows. If there are multiple rows with the same values for all selected columns, only one of them will be returned. This can affect the grouping and summing of data.

2. Grouping Columns: The GROUP BY clause specifies the grouping columns. Make sure that all the necessary columns for grouping are included. It seems that the query already includes the relevant columns for grouping, such as TABHDBHCT.MACUAHANG, TABHDBHCT.MAHDBH, TABHDBH.MABAN, TABHDBH.SOPHIEU, etc.

3. Aggregation Functions: The query uses aggregate functions like SUM to calculate the desired totals. These functions should be applied to the appropriate columns that need to be summed.

4. Additional Columns: Check if there are any additional columns in the SELECT statement that might affect the grouping. It's important to only select the necessary columns for grouping and aggregation.

5. WHERE Clause: Ensure that the conditions specified in the WHERE clause filter the data correctly and do not exclude any necessary rows for grouping.

By reviewing these factors and making appropriate adjustments to the query, you should be able to achieve the desired result of summing and grouping the data into one line.

To group the results and calculate the sum in a single line, you need to remove the individual quantities from the GROUP BY clause and include them in the aggregate functions.

SELECT
    dbo.TABHDBHCT.MACUAHANG,
    dbo.TABHDBHCT.MAHDBH,
    dbo.TABHDBH.MABAN,
    dbo.TABHDBH.SOPHIEU,
    SUM(dbo.TABHDBHCT.SOLUONG * dbo.TABHDBHCT.GIABAN) AS TIENHANG,
    SUM(dbo.TABHDBHCT.SOLUONG * CASE WHEN [MANHOM] = 14 THEN TABHDBHCT.GIABAN ELSE 0 END) AS TTGIO,
    SUM(dbo.TABHDBH.GIAMTGIO) AS TGIAMTGIO,
    SUM(dbo.TABHDBHCT.SOLUONG * dbo.TABHDBHCT.GIAMTIENCK) AS TGIAMTIENCK,
    SUM(dbo.TABHDBHCT.SOLUONG * CASE WHEN [MANHOM] <> 14 THEN (TABHDBHCT.GIABAN - TABHDBHCT.GIAMTIENCK) ELSE 0 END) AS TTDU,
    SUM(dbo.TABHDBH.GIAMTDU) AS TGIAMTDU,
    SUM(dbo.TABHDBHCT.SOLUONG * (dbo.TABHDBHCT.GIABAN - dbo.TABHDBHCT.GIAMTIENCK)) AS TONGTIEN
FROM dbo.TABHDBHCT
INNER JOIN dbo.TABHDBH ON dbo.TABHDBHCT.MAHDBH = dbo.TABHDBH.IDHDBH
WHERE
    dbo.TABHDBHCT.MAHDBH = 62
    AND dbo.TABHDBHCT.MACUAHANG = 1
    AND dbo.TABHDBHCT.DEL = 0
    AND dbo.TABHDBH.DEL = 0
GROUP BY
    dbo.TABHDBHCT.MACUAHANG,
    dbo.TABHDBHCT.MAHDBH,
    dbo.TABHDBH.MABAN,
    dbo.TABHDBH.SOPHIEU
ORDER BY dbo.TABHDBHCT.MAHDBH

SELECT 
    dbo.TABHDBHCT.MACUAHANG, 
    dbo.TABHDBHCT.MAHDBH, 
    dbo.TABHDBH.MABAN, 
    dbo.TABHDBH.SOPHIEU,
    SUM(dbo.TABHDBHCT.SOLUONG * dbo.TABHDBHCT.GIABAN) AS TIENHANG,
    SUM(dbo.TABHDBHCT.SOLUONG * CASE WHEN [MANHOM] = 14 THEN TABHDBHCT.GIABAN ELSE 0 END) AS TTGIO,
    dbo.TABHDBH.GIAMPGIO,
    SUM(dbo.TABHDBH.GIAMTGIO) AS TGIAMTGIO,
    SUM(dbo.TABHDBHCT.SOLUONG * dbo.TABHDBHCT.GIAMTIENCK) AS TGIAMTIENCK,
    SUM(dbo.TABHDBHCT.SOLUONG * CASE WHEN [MANHOM] <> 14 THEN (TABHDBHCT.GIABAN - TABHDBHCT.GIAMTIENCK) ELSE 0 END) AS TTDU,
    dbo.TABHDBH.GIAMPDU,
    SUM(dbo.TABHDBH.GIAMTDU) AS TGIAMTDU,
    SUM(dbo.TABHDBHCT.SOLUONG * (dbo.TABHDBHCT.GIABAN - dbo.TABHDBHCT.GIAMTIENCK)) AS TONGTIEN
FROM dbo.TABHDBHCT
INNER JOIN dbo.TABHDBH ON dbo.TABHDBHCT.MAHDBH = dbo.TABHDBH.IDHDBH
WHERE dbo.TABHDBHCT.MAHDBH = 62 
    AND dbo.TABHDBHCT.MACUAHANG = 1 
    AND dbo.TABHDBHCT.DEL = 0 
    AND dbo.TABHDBH.DEL = 0
GROUP BY 
    dbo.TABHDBHCT.MACUAHANG, 
    dbo.TABHDBHCT.MAHDBH, 
    dbo.TABHDBH.MABAN, 
    dbo.TABHDBH.SOPHIEU,
    dbo.TABHDBH.GIAMPGIO,
    dbo.TABHDBH.GIAMTGIO,
    dbo.TABHDBH.GIAMPDU,
    dbo.TABHDBH.GIAMTDU
ORDER BY dbo.TABHDBHCT.MAHDBH