Sql-server – Query returns repeating arrows when getting sum of a column grouped by some others columns

sql serversum

I have table Inventory and I want to get the sum(quantity) grouped by the columns gid, version, warehouse

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    100   +    name 1    +
|   gid1   +    1     +    1      +    200   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

The following query works, as it returns 2 records with the sum quantity:

SELECT gid, version, warehouse, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse;

+----------+----------+-----------+----------+
|   gid    | version  |  warehouse| quantity |
+----------+----------+-----------+----------+
|   gid1   +    1     +    1      +    300   +
|   gid2   +    1     +    2      +    200   +
+----------+----------+-----------+----------+

I tried to add also the column description in the records:

SELECT gid, version, warehouse, SUM(quantity) as total, description
FROM Inventory
GROUP BY gid, version, warehouse;

but I got an error message:

Column 'description' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause

I searched it and I ended up with this query:

SELECT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse    
) T2
ON T1.gid = T2.gid

It shows the correct sum, but it returns 3 records :

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid1   +    1     +    1      +    300   +    name 2    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

How can I get only the 2 arrows? The following is the desired output:

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

My database is in SQL Server Express 2008.

Best Answer

Three rows are returned because Inventory has three rows. Inner joining the aggregated table won't filter out the three rows. To do that, you can use DISTINCT or simply group by description as well. See this fiddle.

Distinct

SELECT DISTINCT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse    
) T2
ON T1.gid = T2.gid

Group By Description

SELECT gid, version, warehouse, description, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse, description;
Related Question