SQL need to compare rows count values

Question

I have a query that returns the ID, Name and count of the number of times an ID has been entered to the table.

SELECT 
    ID,
    NAME,
    COUNT(*) count
FROM 
    TABLE
GROUP BY
    NAME, ID, CASE_DETAIL_ID
HAVING
    COUNT(*) > 1;

This returns the following data:

ID	NAME	COUNT
123	HAT	10
123	UMBRELLA	10
123	TOWEL	10
123	WATER	8
555	HAT	3
555	UMBRELLA	10
555	TOWEL	10
555	WATER	10
322	UMBRELLA	5
322	TOWEL	20
322	WATER	20

I want to be able to query the row with a count of less than what the other rows with the same ID have. How can I do this? So that the end result is:

ID	NAME	COUNT	FULL COUNT
123	WATER	8	10
555	HAT	3	10
322	UMBRELLA	5	20

There are multiple IDs that we store and I only want the rows/names that have a count less than the rows with the same IDs have.

I have also tried -

WITH x AS
        (SELECT ID, NAME, COUNT(*) count
        FROM FRT.CASE_DETAIL_HISTORY
        GROUP BY
        NAME,
        ID,
        CASE_DETAIL_ID)
SELECT x.ID, t.NAME, X.COUNT, MIN(x.count)
FROM x
JOIN FRT.CASE_DETAIL_HISTORY t
on t.ID= x.ID
GROUP BY x.ID, t.ID, X.COUNT

However, this doesnt give me what I am looking for. I only want rows returned if the name's count doesnt match the 'mode' count of the ID. I also have tried the below but keep facing errors:

WITH COUNT_OF_ROWS AS 
    (SELECT ID, NAME, COUNT(*) count
    FROM TABLE
    GROUP BY NAME, ID, CASE_DETAIL_ID
    HAVING COUNT(*) >= 1),
    
MINIMUM AS
    (SELECT COUNT_OF_ROWS.ID, COUNT_OF_ROWS.NAME, 
MIN(COUNT_OF_ROWS.COUNT) MINI
    FROM COUNT_OF_ROWS
    JOIN TABLE CD on CD.ID = COUNT_OF_ROWS.ID
    GROUP BY COUNT_OF_ROWS.ID, COUNT_OF_ROWS.NAME
)
     
select distinct COUNT_OF_ROWS.*, MINIMUM.MINI
from minimum, count_of_rows
where minimum.mini != count_of_rows.count;

Answer 1

Some sample data would help but you can use a CTE, and select the lowest using min() something like this:

WITH x AS(
    SELECT t.id, t.nametext, COUNT(*) as count
    FROM table t
    GROUP BY id, t.nametext, CASE_DETAIL_ID
), y as(
    SELECT x.id, MIN(x.[COUNT]) as mincount
    FROM x
    GROUP BY x.id
)
select y.id, x.nametext, y.mincount
from y
join x
on x.[COUNT] = y.mincount
and x.id = y.id

Or it can be done using top 1 and order by like this:

SELECT TOP 1 id, name, COUNT(*) as count
FROM TABLE
WHERE ID = 123
GROUP BY NAME, ID, CASE_DETAIL_ID
ORDER BY count DESC

But bare in mind that as this would select only the first row, this would only work with the where clause because it would always only return 1 row.

While if you use the CTE option it would work also if you want per id, without where id = 123.