Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    17

    Unanswered: Max() with Count()

    The following (single table) works as intended:

    SELECT A.* FROM (SELECT Custid, Max(EndDate) as Expiry FROM Registrations GROUP BY Custid) X
    JOIN Registrations A ON A.Custid=X.custid and A.EndDate=X.Expiry;

    It shows for each customer his expiry date.
    Now I need to add an extra column showing the count per customer (Count(custid)) but all my attempts have been so far unsuccessful.
    Help will be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.* 
         , x.daCount
      FROM ( SELECT Custid
                  , MAX(EndDate) as Expiry 
                  , COUNT(*) AS daCount
               FROM Registrations 
             GROUP 
                 BY Custid ) AS x
    INNER
      JOIN Registrations AS a
        ON a.Custid = x.Custid 
       AND a.EndDate = x.Expiry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    17
    Superb - many thanks Rudy; I would have never been able to figure it out on my own. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •