can this multiple self-JOIN be optimized?

user_10386777 0
Viewed 75

Sorry if the title isn't clear enough. This is my query:

SELECT
    '>200000 €' as amount,
    Q1.asset as inbound,
    Q2.asset as outbound,
    Q3.asset as inside
FROM
    (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND direction = 'inbound'
        AND to_address is null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q1
JOIN
        (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND direction = 'outbound'
        AND to_address is null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q2
JOIN 
        (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND to_address is not null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q3

as you can see, I'm joining three queries which have some conditions in common. My question is: Is it possible to write only once the common conditions?

aggregate-functions mysql sql
2 Answers

The intention of your example query is unclear to me, so my answer might not produce the results you are looking for. I am confused why COUNT() and not SUM() is involved.

In any case you can apply conditional aggregation because all values come from the same table. No JOINs needed here.

If you are trying to count all transactions that are > 200000 over some different criteria you can do:

SELECT
    '>200000 €' as amount,
    SUM(CASE WHEN direction = 'inbound' AND to_address is null 
        THEN 1 ELSE 0 END) AS inbound,
    SUM(CASE WHEN direction = 'outbound' AND to_address is null 
        THEN 1 ELSE 0 END) AS outbound,
    SUM(CASE WHEN to_address is not null 
        THEN 1 ELSE 0 END) AS inside
FROM user_wallet_movement
WHERE 
    (amount*base_rate) > 200000
    AND status = 'execute' 
    AND mov_date > '2020-01-07'
    AND mov_date < '2021-06-30'

And if you want the total SUM of all transactions and also subtotals over inbound/outbound/inside you can do:

SELECT
    SUM(amount*base_rate) as total,
    SUM(CASE WHEN direction = 'inbound' AND to_address is null 
        THEN amount*base_rate ELSE 0 END) AS inbound,
    SUM(CASE WHEN direction = 'outbound' AND to_address is null 
        THEN amount*base_rate ELSE 0 END) AS outbound,
    SUM(CASE WHEN to_address is not null 
        THEN amount*base_rate ELSE 0 END) AS inside
FROM user_wallet_movement
WHERE 
    AND status = 'execute' 
    AND mov_date > '2020-01-07'
    AND mov_date < '2021-06-30'
user_439590

You can use case expression

select
    '>200000 €' as amount
    sum(case when direction = 'inbound' then amount* base_rate else 0 end) as inbound_asset,
    sum(case when direction = 'outbound' then amount* base_rate else 0 end) as outbound_asset,
    sum(case when direction = 'execute' then amount* base_rate else 0 end) as execute_asset
FROM user_wallet_movement
WHERE to_address is not null
AND mov_date > '2020-01-07'
AND mov_date < '2021-06-30'
AND amount* base_rate > 200000
user_86913190
Related