CODEWITHSUNDEEP
sqlsql-server
KY Leung
KY Leung

Reputation: 103

Finding the least in Count(*)

I'm trying to find all LID who have committed the least during a given time period. Following is my table:

| requestDate |    BID    |    LID    | PercentageContributed | DateComitted
| 1/10/2016   | S7444319C | S2262450A |           0.25        |   18/10/2016
| 1/10/2016   | S7444319C | S4495282I |           0.25        |   7/10/2016
| 1/10/2016   | S7444319C | S4792394D |           0.25        |   28/10/2016
| 1/10/2016   | S7444319C | S7173102K |           0.25        |   9/10/2016
| 25/10/2016  | S9981233W | S2848191X |           1.00        |   13/11/2016
| 28/10/2016  | S7611209X | S2848191X |           0.33        |   13/11/2016
| 28/10/2016  | S7611209X | S4792394D |           0.33        |   4/11/2016
| 28/10/2016  | S7611209X | S7145303Q |           0.33        |   5/11/2016

I have tried the following code:

SELECT LID, COUNT(*) AS TotalNumCommitte
FROM Commits
WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID

But I only managed to get the following:

|    LID    | TotalNumCommitte
| S2262450A |        1
| S2848191X |        2
| S4495282I |        1
| S4792394D |        2
| S7145303Q |        1
| S7173102K |        1

What I want to achieve is the following:

|    LID    | TotalNumCommitte
| S2262450A |        1
| S4495282I |        1
| S7145303Q |        1
| S7173102K |        1

I have also tried the following:

SELECT MIN(TotalNumCommitted)
FROM (
    SELECT LID, COUNT(*) AS TotalNumCommitted
    FROM Commits
    WHERE DateCommitted >= '2015-11-03'
    AND DateCommitted <= '2017-01-09'
    GROUP BY LID
) T1

But I only get 1 (which is the minimum) Thank you so much for your help.

Upvotes: 0

Views: 72

Answers (6)

user330315
user330315

Reputation:

You can combine your group by query with a window function:

select lid, totalnumcommitte
from (
    select lid, 
           count(*) as totalnumcommitte, 
           rank() over (order by count(*)) as rnk
    from commits
    group by lid
) t
where rnk = 1
order by lid;

Or using a common table expression:

with counts as (
    select lid, 
           count(*) as totalnumcommitte, 
           rank() over (order by count(*)) as rnk
    from commits
    group by lid
)
select lid, totalnumcommitte
from counts
where rnk = 1
order by lid;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

In standard SQL you'd use FETCH FIRST ROW WITH TIES as in

SELECT LID, COUNT(*) AS TotalNumCommitte
FROM Commits
WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID
ORDER BY COUNT(*)
FETCH FIRST ROW WITH TIES;

In SQL Server you must use TOP (1) WITH TIES instead:

SELECT TOP (1) WITH TIES LID, COUNT(*) AS TotalNumCommitte
FROM Commits
WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID
ORDER BY COUNT(*);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

I think the easiest way is TOP WITH TIES:

SELECT TOP (1) WITH TIES LID, COUNT(*) AS TotalNumCommitte
FROM Commits
WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID
ORDER BY TotalNumCommitte;

Upvotes: 2

Jack Reilly
Jack Reilly

Reputation: 168

SELECT LID, COUNT(*) AS TotalNumCommitte FROM Commits WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID 
HAVING COUNT(*) =
(SELECT TOP 1 COUNT(*) AS TotalNumCommitted FROM Commits WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID ORDER BY TotalNumCommitted)

Upvotes: 0

Thys
Thys

Reputation: 121

You can filter the results of the aggregate function (count in this case), by using HAVING, for example:

SELECT LID, COUNT(*) AS TotalNumCommitte
FROM Commits
WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
GROUP BY LID HAVING COUNT(*) = 1

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

Assuming a modern SQL with support for Common Table Expressions and ranking functions, something like this:

;WITH Counts as (
    SELECT LID, COUNT(*) AS TotalNumCommitte
    FROM Commits
    WHERE DateCommitted BETWEEN '2015-11-03' AND '2017-01-09'
    GROUP BY LID
), RankedCounts as (
    SELECT *,RANK() OVER (ORDER BY TotalNumCommitte) as rk
    FROM Counts
)
select *
from RankedCounts
where rk=1

Upvotes: 2

Related Questions