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.

CodePudding user response：

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"

CodePudding user response：

Try this,

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 "Last_Order_Date"
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         ,
                totals.cnt_orders ,
                totals.sum_amount