I don’t think my post title is correct/the best but I don’t really know how to concisely explain it.
I have one SQL query
SELECT a.id, c.firstname AS "First Name", c.lastname AS "Last Name", c.telephone AS "Telephone", c.email AS "Email", a.name AS "Company Name", i.available_integration_id AS 'Stripe' FROM users u INNER JOIN contacts c ON c.id = u.contact_id INNER JOIN accounts a ON u.account_id = a.id LEFT JOIN integrations i ON a.id = i.account_id WHERE a.id IN (SELECT DISTINCT a1.id FROM accounts a1 INNER JOIN orders o1 ON a1.id = o1.account_id WHERE o1.created_at >= '2020-10-01 00:00:00' )
This returns 600+ results
I then need to run
SELECT count(id), sum(amount) FROM transactions t WHERE t.order_id IN (SELECT o.id FROM orders o WHERE o.account_id = 'c59bde2a-3ff8-61ea-e449-42699342a444' AND o.status = 'completed' ) AND t.status = 'completed'
against every ID returned from the first query.
Is it possible to merge these two queries together to return one table that looks like this
ID, First Name, Last Name, Telephone, Email, Company Name, Stripe, Total Orders, Total Amount
or do I have to run the second query 600+ times and manually copy/paste the data over?
Advertisement
Answer
If I’m not missing something, this is what you need:
SELECT a.id, c.firstname AS "First Name", c.lastname AS "Last Name", c.telephone AS "Telephone", c.email AS "Email", a.name AS "Company Name", i.available_integration_id AS "Stripe", totals.cnt_orders AS "Total Orders", totals.sum_amount AS "Total Amount" FROM users u INNER JOIN contacts c ON c.id = u.contact_id INNER JOIN accounts a ON u.account_id = a.id LEFT JOIN integrations i ON a.id = i.account_id LEFT JOIN (SELECT a.id, COUNT(t.id) AS cnt_orders, SUM(t.amount) AS sum_amount FROM accounts a INNER JOIN orders o ON o.account_id = a.id INNER JOIN transactions t ON o.id = t.order_id WHERE o.status = 'completed' AND o.created_at >= '2020-10-01 00:00:00' GROUP BY a.id) totals ON a.id = totals.id;