mark mark - 4 months ago 10
MySQL Question

SQL Query - Getting Sum Of Multiple Column with Id in Table Using Inner Join

Good day,

I'm creating a query where I can throw inside the data grid view.
Suppose I have 2 table namely table A and table B. Table A has StaffId column and while Table B has StaffId too. Table B can have multiple value of cost with foreign key of StaffId. What I'm trying to do is, get all the sum value inside the Table B with the same StaffId.

Here's the sample table definitions and contents.

// Table A
| StaffId | Name |
| 1 | Dummmy |

// Table B
| Id | StaffId | Cost |
| 1 | 1 | 10.00 |
| 2 | 1 | 10.00 |
| 3 | 1 | 10.00 |


I already tried this query, but I can't get the correct answer.

SELECT A.Name, Sum(B.Cost) FROM B INNER JOIN A ON A.StaffId=B.StaffId


The answer should be like this

Dummy | 30.00


but my query doesn't work. How can I get the value of all the cost in the table B?

Any help would be much appreciate. Thank you in advance.

Answer

Try like this,

To find sum of cost for each and every StaffId's you should use this.

SELECT A.StaffId
    ,A.NAME
    ,Sum(B.Cost) as Total
FROM B
INNER JOIN A ON A.StaffId = B.StaffId
GROUP BY A.StaffId
    ,A.NAME

To find all the cost for all the StaffId's you should use this.

SELECT Sum(B.Cost)
FROM B
INNER JOIN A ON A.StaffId = B.StaffId
Comments