Efficiently get min,max, and summary data

I have a table of accounts and a table of transactions. In a report I need to show the following for each account:

First Purchase Date, 
First Purchase Amount, 
Last Purchase Date, 
Last Purchase Amount, 
# of Purchases, 
Total of All Purchases.

The transaction table looks like this:

TX_UID
Card_Number
Post_Date
TX_Type
TX_Amount

Currently the query I've inherited has a sub-query for each of these elements. It seems to me that there's got to be a more efficient wa开发者_Python百科y. I'm able to use a stored procedure for this and not a single query.

A sample of a query to get all transactions for a single account would be:

select * from tx_table where card_number = '12345' and TX_Type = 'Purchase'

Any ideas?

try this:

select tt1.post_date as first_purchase_date,
       tt1.tx_amount as first_purchase_amount,
       tt2.post_date as last_purchase_date,
       tt2.tx_amount as last_purchase_amount,
       tg.pc as purchase_count,
       tg.amount as Total
from (select Card_Number,min(post_date) as  mipd, max(post_date) as mxpd, count(*) as pc, sum(TX_Amount) as Amount from tx_table where TX_Type = 'Purchase' group by card_number) tg
join tx_table tt1 on tg.card_number=tt1.card_number and tg.mipd=tt1.post_date
join tx_table tt2 on tg.card_number=tt2.card_number and tg.mxpd=tt2.post_date
where TX_Type = 'Purchase'

I added the count .. I didn't see it first time.

If you need also the summary on multiple TX_Types, you have to take it from the where clause and put it in the group and the inner selection join. But I guess you need only for purchases

;with cte as
(
  select
    Card_Number,
    TX_Type,
    Post_Date,
    TX_Amount,
    row_number() over(partition by TX_Type, Card_Number order by Post_Date asc) as FirstP,
    row_number() over(partition by TX_Type, Card_Number order by Post_Date desc) as LastP
  from tx_table 
)
select 
  F.Post_Date as "First Purchase Date",
  F.TX_Amount as "First Purchase Amount",
  L.Post_Date as "Last Purchase Date", 
  L.TX_Amount as "Last Purchase Amount",
  C.CC as "# of Purchases",
  C.Amount as "Total of All Purchases"
from (select Card_Number, TX_Type, count(*) as CC, sum(TX_Amount) as Amount
      from cte
      group by Card_Number, TX_Type) as C
  inner join cte as F
    on C.Card_Number = F.Card_Number and
       C.TX_Type = F.TX_Type and
       F.FirstP = 1    
  inner join cte as L
    on C.Card_Number = L.Card_Number and
       C.TX_Type = L.TX_Type and
       L.LastP = 1