Merge/looping through an sql query with another query?-CodePudding

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?

CodePudding user response：

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;