CODEWITHSUNDEEP
sqlsum
Ashton Sheets
Ashton Sheets

Reputation: 513

SQL sum field when column values match

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!

Upvotes: 12

Views: 62805

Answers (2)

Kermit
Kermit

Reputation: 34063

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

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

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

SQL Fiddle Example

Upvotes: 19

Related Questions