CODEWITHSUNDEEP
phpmysqlsqlsum
Tom
Tom

Reputation: 425

Mysql Query SUM dynamic amounts

I need help writing a mysql query to SUM amounts in a column where another columns values may be different depending on the value that it contains. Here is an example of my table.

account     type         amount
-------     ----         ------
Dell        Inventory    500.00
Microsoft   Inventory    100.00
Cisco       Inventory    300.00
Dell        Inventory    400.00
Webhosting  Service       15.00

Here is what I have so far, its not much but I can't think of a good way to do this.

$result = mysql_query("SELECT * FROM table WHERE type = 'Inventory'");
while($row = mysql_fetch_array($result)){
  Sum the amounts for each account type.  The account types are not fixed, will change regularly
}

As my note above says, the account fields will not be fixed so I cannot hard code my queries with the names of the account.

Then I would like to have a report detailing the totals of each account.

Dell $900
Microsoft $100
Cisco $300

Thanks for your help!

Upvotes: 1

Views: 1231

Answers (3)

Srikanth Kolli
Srikanth Kolli

Reputation: 912

SELECT `account`,sum(`amount`) FROM `table_name` WHERE `type`='Inventory' group by `account`

Upvotes: 1

Fabio
Fabio

Reputation: 23510

I think you can use GROUP BY to group your records by account and so you can use SUM() function to sum all amounts 

SELECT account, SUM(amount) as total
FROM  table
WHERE type = 'inventory'
GROUP BY account

Upvotes: 1

John Woo
John Woo

Reputation: 263893

You need to use an aggregate function SUM() to get the total amount for every account.

SELECT  account, SUM(amount) totalAmount
FROM    tableName
WHERE   type = 'inventory'
GROUP   BY account

Upvotes: 4

Related Questions