This is my SQl, I’m looking to add an extra column that will display the date of the last order a customer has made. This is stored in the orders table in the created_at column.
SELECT DISTINCT a.id ,
a.name ,
c.email ,
u.activated ,
i.available_integration_id ,
totals.cnt_orders AS "Total Orders",
totals.sum_amount AS "Total Amount"
FROM accounts a
INNER JOIN users u
ON a.id = u.account_id
INNER JOIN contacts c
ON u.contact_id = c.id
LEFT JOIN integrations i
ON a.id = i.account_id
LEFT JOIN orders o
ON a.id = o.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 t.status = 'completed'
GROUP BY a.id
)
totals
ON a.id = totals.id
This is what I get when I run the above SQL. I basically want an additional column that has the created_at value from the last order a customer has made.
I tried just adding o.created_at in the select but that results in a row for each order which is not what I want.
>Solution :
This appears to be just a simple aggregate function max(date) group by all non-aggregated fields
SELECT a.id ,
a.name ,
c.email ,
u.activated ,
i.available_integration_id ,
totals.cnt_orders AS "Total Orders",
totals.sum_amount AS "Total Amount",
max(o.created_at) as Most_Recent_Order
FROM accounts a
INNER JOIN users u
ON a.id = u.account_id
INNER JOIN contacts c
ON u.contact_id = c.id
LEFT JOIN integrations i
ON a.id = i.account_id
LEFT JOIN orders o
ON a.id = o.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 t.status = 'completed'
GROUP BY a.id
)
totals
ON a.id = totals.id
GROUP BY a.id ,
a.name ,
c.email ,
u.activated ,
i.available_integration_id ,
"Total Orders",
"Total Amount"