GROUP BY query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm running a grouping query as follows;
SELECT Client, balance, SUM(payments), COUNT(payments), MAX(date_received)
FROM customers
GROUP BY Client, balance

I would like to include a column for a payment made on MAX(date_received).
Any suggestions?

thanks,

Pedro
 
You will need two queries or a subquery for when you pull a single date your
sums are only for that date.
 
Dear Pedro:

In order to see one or more other columns from the specific row that
contains the MAX(date_received) you will need a correlated subquery:

SELECT Client, balance, SUM(payments), COUNT(payments), MAX(date_received),
(SELECT payment
FROM customers T1
WHERE T1.Client = T.Client
AND date_received =
(SELECT date_received
FROM customers T2
WHERE T2.Client = T1.Client))
AS RecentPayment
FROM customers T
GROUP BY Client, balance

Please let me know if this helped, and if you require any other assistance.

Tom Ellison
 
Back
Top