CODEWITHSUNDEEP
phpmysql
ThisIsLegend1016
ThisIsLegend1016

Reputation: 105

MySQL count value by ID

Not sure if the title explains what I am trying to do correctly however I was unable to word it.

I am trying to create an output for a some graphs. The output I am aiming for is just the total value of some rows in a mySQL table grouped by a category ID. My tables are as follows:

Transactions:

TransactiondID | TransactionName |TransactionCategory| Value
*************************************************************
       1       |  GenericText1   |      1            | 30
       2       |  GenericText2   |      1            | 38
       3       |  GenericText2   |      2            | 38

And my Reference Data for TransactionCategory

TranCatID | TransCatName
*************************
    1     | Tranportation
    2     | Petrol

So what I want to do is do is a SUM of the value field by each category. So I would get an output of.

Category       | Value
****************************
Transportation | 68
Petrol         | 30

I got this but it does not work. I think it is completely incorrect but wanted to show my attempt.

SELECT SUM( `TransactionValue`) AS Value, 
transaction_category.transaction_category
FROM Transactions 
JOIN transaction_category on Transactions.TransactionID = transaction_category_id

Upvotes: 1

Views: 53

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use this :

SELECT tc.TransCatName Category, SUM(t.Value) as Value
FROM TransactionCategory tc
LEFT JOIN Transactions t ON tc.TranCatID = t.TransactionCategory
group by tc.TransCatName

SQL HERE

OUTPUT

Category       | Value
-----------------------
Petrol         | 38
Transportation | 68

Please notice the SUM for PETROL, it should 38 as above, which is wrongly written 30 in your question description!

Upvotes: 0

Pankaj Kumar Gunwant
Pankaj Kumar Gunwant

Reputation: 34

Grouping by either "TransactionCategory" or "TranCatID" will give you the desired result shown as follows:

SELECT TransactionCategory.TransCatName, SUM( `Value`) AS Value FROM Transactions  JOIN TransactionCategory on Transactions.TransactionCategory = TransactionCategory.TranCatID GROUP BY TransactionCategory.TransactionCategory;

or

SELECT TransactionCategory.TransCatName, SUM( `Value`) AS Value FROM Transactions  JOIN TransactionCategory on Transactions.TransactionCategory = TransactionCategory.TranCatID GROUP BY TransactionCategory.TranCatID;

Upvotes: 1

Carl Binalla
Carl Binalla

Reputation: 5401

This should do the trick

SELECT TransactionCategory.TransCatName,
    SUM(Transactions.Value) as Value
FROM Transactions
    LEFT JOIN TransactionCategory ON TransactionCategory.TranCatID = Transaction.TransactionCategory

Upvotes: 0

Related Questions