0

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.

  • How can I get only the 2 arrows? Show the result which you want to obtain in table form instead of explaination how it is to look like... and specify server version. – Akina Oct 17 at 6:06
  • Does you guarantee that description has the same value for all records in (gid, version, warehouse) group? If not - what description from all possible values must be returned? – Akina Oct 17 at 6:16
  • Yes, it's the same value... – yaylitzis Oct 17 at 6:17
2

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;
  • Ok, the solution was simple! :) Thanks, for the extra solution with theDistinct parameter. – yaylitzis Oct 17 at 6:18
1

One more solution, which is applicable even when the (gid, version, warehouse) group contains different description values.

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

Only one description value from all values in a group is returned. MIN() may be used too.

Of course it is applicable when all records within a group contains the same value.

  • Thanks for the info! – yaylitzis Oct 17 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.