CODEWITHSUNDEEP
mysqlsql
misterone
misterone

Reputation: 361

Combine my two MySQL queries

This is erp.orders:

userId  paidAt
1       2017-06-30
1       2017-06-18
2       2017-06-07
4       2017-06-07
3       2017-01-01
2       2017-01-01
2       2017-01-01
2       2017-01-01

This is prod.referral_order_delivered

user_id
1
2
1
1
1

I need to combine two requests.

This request :

SELECT DISTINCT 
    erp.orders.userid, COUNT(erp.orders.userid) as countorders
FROM 
    erp.orders
WHERE 
    erp.orders.userid IN (SELECT erp.orders.userid
                          FROM erp.orders, prod.referral_order_delivered
                          WHERE erp.orders.userid = prod.referral_order_delivered.user_id
                            AND erp.orders.paidat::date >= '2017-06-07'
                            AND erp.orders.paidat::date <= '2017-07-07')
GROUP BY 
    erp.orders.userid;

This returns this result:

userId  countorders
1           2
2           4

And this request:

SELECT 
    prod.referral_order_delivered.user_id,
    COUNT(prod.referral_order_delivered.user_id) AS countreferral
FROM 
    prod.referral_order_delivered
WHERE 
    prod.referral_order_delivered.user_id IN (SELECT DISTINCT erp.orders.userid
                                              FROM erp.orders
                                              INNER JOIN prod.referral_order_delivered ON erp.orders.userid = prod.referral_order_delivered.user_id
                                              WHERE erp.orders.paidat >= '2017-06-07'
                                                AND erp.orders.paidat <= '2017-07-07')
GROUP BY 
    prod.referral_order_delivered.user_id

which returns this result:

user_id    countreferral
1          4
2          1

Now I want to combine these requests to get this result:

userId  countorders countreferral
1       2           4
2       4           1

Note that user_id and userId is the same thing. So it doesn't matter if this is user_id or userId which appears.

You can test the solution here.

Upvotes: 2

Views: 63

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider joining the two aggregate queries as derived tables. First, convert the IN clause queries (both of which are equivalent with former using implicit join and latter with explicit join) to JOIN clauses. Then, run same aggregations with outer query joining both and selecting columns.

SELECT agg1.userid, agg1.countorders, agg2.countreferral
FROM
   (SELECT j.userid, COUNT(j.userid) as countorders
    FROM
        (SELECT DISTINCT erp.orders.userid
         FROM erp.orders
         INNER JOIN prod.referral_order_delivered 
             ON erp.orders.userid = prod.referral_order_delivered.user_id
         WHERE erp.orders.paidat >= '2017-06-07'
           AND erp.orders.paidat <= '2017-07-07') j

    INNER JOIN erp.orders e ON j.userid = e.userid
    GROUP BY j.userid) agg1

INNER JOIN   
   (SELECT j.userid, COUNT(j.userid) as countreferral
    FROM
        (SELECT DISTINCT erp.orders.userid
         FROM erp.orders
         INNER JOIN prod.referral_order_delivered 
             ON erp.orders.userid = prod.referral_order_delivered.user_id
         WHERE erp.orders.paidat >= '2017-06-07'
           AND erp.orders.paidat <= '2017-07-07') j

    INNER JOIN prod.referral_order_delivered p ON j.userid = p.user_id
    GROUP BY j.userid) agg2

ON agg1.userid = agg2.userid

Even save the nested inner query used in the source of both aggregates as a view. 

SELECT agg1.userid, agg1.countorders, agg2.countreferral
FROM
   (SELECT v.userid, COUNT(j.userid) as countorders
    FROM myview v           
    INNER JOIN erp.orders e ON v.userid = e.userid
    GROUP BY v.userid) agg1

INNER JOIN   
   (SELECT v.userid, COUNT(j.userid) as countreferral
    FROM myview v         
    INNER JOIN prod.referral_order_delivered p ON v.userid = p.user_id
    GROUP BY v.userid) agg2

ON agg1.userid = agg2.userid

In other RDBMS's like Postgres unlike MySQL you could use CTE's WITH() to avoid repetition. See rextester demo forked from your initial setup.

Upvotes: 1

Related Questions