Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL sum field when column values match

Tags:

sql

sum

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!

like image 511
Ashton Sheets Avatar asked Oct 03 '12 16:10

Ashton Sheets


2 Answers

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

SQL Fiddle Example

like image 144
D'Arcy Rittich Avatar answered Oct 12 '22 13:10

D'Arcy Rittich


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
like image 35
Kermit Avatar answered Oct 12 '22 13:10

Kermit