MySql multiple results tables for each unique values

lozadakb 0
Viewed 26

Sorry if the title is not well explained tried my best.

I currently have this transactions table which hold the records, every row has an agent and a currency assigned to it.

id amount agent currency_id
1 400.00 agent1 1
2 170.00 agent5 3
3 110.00 agent4 2
4 430.00 agent5 3
5 155.00 agent1 1
6 370.00 agent2 2
7 10.00 agent2 2
8 150.00 agent1 1
9 130.00 agent3 3
10 445.00 agent4 2

And this other table called currency which holds the unique currency and name.

id currency
1 USD
2 VES
3 EUR

The query that I want to make is a SUM and group by agent for every currency there is. I am able to do it with a single query like this but only for one currency in the WHERE clause:

SELECT a.agent, 
       SUM(a.amount) 
FROM transactions AS a 
INNER JOIN currency AS b ON b.id = a.currency_id
WHERE b.currency = 'VES'
GROUP BY a.agent

I will be getting this result which is only for the VES currency

agent total
agent2 380.00
agent4 555.00

I am looking for one query that allow me to get the result of all 3 current existing currencies (USD, VES, EUR) this should give a result of 3 different tables

mysql sql
1 Answers

I suspect that you want a report showing all agents, currencies, and their sums. You may try using this cross join approach:

SELECT a.agent, c.currency, COALESCE(SUM(t.amount), 0) AS total
FROM (SELECT DISTINCT agent FROM transactions) a
CROSS JOIN currency c
LEFT JOIN transactions t
    ON t.agent = a.agent AND
       t.currency_id = c.id
GROUP BY 1, 2
ORDER BY 1, 2;

The first two tables in the join generate all combinations of agents and currencies. We join this to your transactions table and aggregate to get the totals.

Tim Biegeleisen0
Related