SQL query include latest date in count-CodePudding

I have this sample where I am counting the number of records and displaying them as CustomerIDCount. I would also like to include the LatestDate but I am not sure if I need another query or I can add it to this one?

SQLFiddle

Setup

CREATE TABLE log
(
    [CustomerID] [int] NULL, 
    [LogDate] [datetime] NULL
);
    
INSERT INTO log ([CustomerID], [LogDate])
VALUES
    (1, 2021-02-12),
    (2, 2021-02-12),
    (1, 2021-02-12),
    (3, 2021-03-12),
    (4, 2021-02-12)
;

My attempt:

SELECT
    CustomerID, COUNT(*) CustomerIDCount
FROM
    (SELECT  CustomerID
     FROM log) g
GROUP BY
    CustomerID
ORDER BY 
    CustomerIDCount

Results:

CustomerID	CustomerIDCount
2	1
3	1
4	1
1	2

But my desired results would be:

CustomerID	CustomerIDCount	LatestDate
2	1	2021-02-12
3	1	2021-03-12
4	1	2021-02-12
1	2	2021-03-12

CodePudding user response：

You don't need to use a sub-query, and you can just add Max(LogDate) to the query.

select CustomerID, count(*) CustomerIDCount, max(LogDate) as max_LogDate
from log
group by CustomerID
order by CustomerIDCount

CodePudding user response：

Hard to tell from this limited information, and am assuming you have a Group By on customer ID, with a COUNT. Can you just add MAX(LatestDate) to that query?

CodePudding user response：

SELECT CustomerID
     , Count(*) AS CustomerIDCount
     , Max(LogDate) AS LastestDate
FROM   log
GROUP
    BY CustomerID
;