Group BY problem with counting in SQL

I have the following code:

    SELECT
    ir.[Id]
    ,ir.[DriverLicenseNumber]
    ,COUNT(ir.[ReportNumber]) As [TotatReports]
    ,ir.[LastName]
    ,ir.[FirstName]
    FROM [dbo].[InterimReports] ir
GROUP BY ir.[DriverLicenseNumber],ir.[Id],ir.[LastName],ir.[FirstName]

This i开发者_如何学JAVAs still giving me the same drivers license in multiple records. I only want to see 1 unique drivers license for each license. Then I want to count how many reports that the drivers license number has related to it.

If you just want to see, as you say, unique driver's licences and their counts, then you just leave out the Id column of your query, because it seems to be the most probable cause why your query returns duplicates which you didn't expect.

Here:

SELECT
    ir.[DriverLicenseNumber]
    ,COUNT(ir.[ReportNumber]) As [TotatReports]
    ,ir.[LastName]   /* if these two are not related to DriverLicenseNumber */
    ,ir.[FirstName]  /* they should probably be left out as well */
FROM [dbo].[InterimReports] ir
GROUP BY ir.[DriverLicenseNumber]
    ,ir.[LastName],ir.[FirstName]  /* same restriction applies */

I know that it has already been said in comments to your question, so I shouldn't be bothered and what Quassnoi has suggested is doing essentially the same thing. Only that solution looks a bit overkill and will be slower than simple grouping. Also, why would you need the Id column in the results?

So, I thought it would be all right if the (probably better) alternative could be made more overt both to you and to those interested.

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY driverlicensenumber ORDER BY id) rn
                COUNT(*) OVER (PARTITION BY driverlicensenumber) cnt
        FROM    interimreports
        )
SELECT  *
FROM    q
WHERE   rn = 1