CODEWITHSUNDEEP
phpmysql
Progger
Progger

Reputation: 2254

How do I calculate discount and add up the totals for records returned - PHP / MySQL

I have a table called 'shares' which contain asset_id, , asset_value, member_id, and percent_ownership. I am trying to sum up the value of shares for a specific member. For example: member_id #1 has the following shares:

| asset_id  asset_value  member_id  percent_owner
| --------  -----------  ---------  -------------
|    1          300          1           100
| -----------------------------------------------
|    2          300          1            50
| -----------------------------------------------

If coded properly, my query should return $450 (100% of 300 + 50% of 300).

I am thinking of having MySQL do the math for each individual record and store it in an array using PHP. PHP will then sum everything up. I am very new to MySQL and PHP so please bear with me.

Thanks!

Upvotes: 0

Views: 590

Answers (4)

liquorvicar
liquorvicar

Reputation: 6106

You need to read up on aggregate queries in MySQL but to get you started you will probably end up with something like

SELECT
    member_id,
    [do your maths here]
FROM
    table
GROUP BY
    member_id

Have a go yourself and if you get stuck post any specific problems you have back here.

Upvotes: 0

Adrian Cornish
Adrian Cornish

Reputation: 23868

SELECT (asset_value * percent_owner) / 100 AS shares FROM table;

Upvotes: 0

Marc B
Marc B

Reputation: 360672

SELECT member_id, SUM(asset_value * percent_owner / 100)
FROM ...
WHERE ...
GROUP BY member_id

would return all of the member IDs and their share values.

Upvotes: 3

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Select Sum(Asset_value * (percent_ower / 100)) From shares Where Member_id = 1

Upvotes: 0

Related Questions