sql server - SQL First and Last Transaction Report

Question

I have a table in SQL, that looks a little similar to the table below:

[enter image description here]

My goal is to generate a first and Last Transaction report. I want to know when did customer X make their first purchase and what is the date of their most recent purchase. I would like to group my results by Store and add all the transaction if they happen on the same day.

For instance, if John do made 2 expenses at walmart on Jan 15th and that's their most recent transaction, I would like those two expenses to be Summed in my report.

Here is the final result I'd expect from a table like on the example above:

[enter image description here]

With what I have tried so far, I am only getting 1 value back

The SQL looks a little similar to

Select 
SN
, SID
, CustomerName
, BankAccount
, Min(TransDate)
, Max(TransDate)
, price
, store
From transaction
GROUp by 
SN
, SID
, CustomerName
, BankAccount
, Min(TransDate)
, Max(TransDate)
, price
, store

I know I have to use some types of nested query to get the result(maybe) but I have been unsuccessful.

question from:https://stackoverflow.com/questions/65852015/sql-first-and-last-transaction-report

Answer 1

here is one way , double group by :

SELECT
    SN    , SID    , CustomerName    , BankAccount    , store
    , MIN(TransDate) firsttrans
    , MAX(TransDate) lasttrans
    , price          lasttransprice
FROM
    (
        SELECT
            SN, SID , CustomerName, BankAccount, store , TransDate
            , SUM(price) price
        FROM
            TRANSACTION
        GROUP BY
            SN, SID, CustomerName, BankAccount, TransDate, store
    ) xx
GROUP BY
    SN, SID, CustomerName, BankAccount, store, price