SQL sum field when column values match

Question

The title was hard to word but the question is pretty simple. I searched all over here and could not find something for my specific issue so here it is. I'm usuing Microsoft SQL Server Management Studio 2010.

Table Currently looks like this

  | Value | Product Name|
  |  300  | Bike        |
  |  400  | Bike        |
  |  300  | Car         |
  |  300  | Car         |

I need the table to show me the sum of Values where Product Name matches - like this

  | TOTAL | ProductName |
  |  700  | Bike        |
  |  600  | Car         |

I've tried a simple

 SELECT
      SUM(Value) AS 'Total'
      ,ProductName
 FROM TableX

But the above doesn't work. I end up getting the sum of all values in the column. How can I sum based on the product name matching?

Thanks!

D'Arcy Rittich · Accepted Answer · 2012-10-03 16:55:51Z

19

SELECT SUM(Value) AS 'Total', [Product Name]
FROM TableX  
GROUP BY [Product Name]

SQL Fiddle Example

answered Oct 3, 2012 at 16:55

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashton Sheets Over a year ago

I knew it should be something simple! This worked just fine. Thanks =)

Kermit · Accepted Answer · 2012-10-03 17:33:07Z

4

Anytime you use an aggregate function, (SUM, MIN, MAX ... ) with a column in the SELECT statement, you must use GROUP BY. This is a group function that indicates which column to group the aggregate by. Further, any columns that are not in the aggregate cannot be in your SELECT statement.

For example, the following syntax is invalid because you are specifying columns (col2) which are not in your GROUP BY (even though MySQL allows for this):

SELECT col1, col2, SUM(col3)
FROM table
GROUP BY col1

The solution to your question would be:

SELECT ProductName, SUM(Value) AS 'Total'
FROM TableX
GROUP BY ProductName

edited Oct 3, 2012 at 17:33

answered Oct 3, 2012 at 17:16

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

2 Comments

D'Arcy Rittich Over a year ago

Anytime you use an aggregate function, (SUM, MIN, MAX ... ) you must use GROUP BY. - This is incorrect, you may wish to aggregate on the entire set, or on a portion restricted by a WHERE clause. See SQL Fiddle Example

Kermit Over a year ago

@RedFilter Should have explicitly stated this applies when using non-aggregated columns in the SELECT list. I've updated my answer to reflect this.

Collectives™ on Stack Overflow

SQL sum field when column values match

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related