Jobert Enamno Jobert Enamno - 6 months ago 15
SQL Question

Get Total Number of Records after Distinct SQL Server

I have a query that is joined to 3 tables. Without "DISTINCT" the total records is 331 but with Distinct the total number is 113. I want to get the 113 total only which is the total of distinct records. I used Count but it gives me the total number of not unique records. Please help me get the total of distinct records. Here's my query.


Without distinct (331 records)


SELECT
uf.OrigFileName,
uf.CreatedOn,
sdiTran.Status,
sdiFS.FileName,
sdiFile.ArchiveLogID,
COUNT(*) over() as totalRows
FROM [SDI].dbo.UploadedFile uf
inner join [SDI].dbo.SDIFile sdiFile on uf.UploadedFullFileName = sdiFile.OriginalName
left join [SDI].dbo.SDITransaction sdiTran on sdiFile.ID = sdiTran.SDIFileID
inner join [SDI].dbo.SDIFSArchive sdiFS on sdiFile.ID = sdiFS.SDIFileID
WHERE uf.CommunityID = '7cc67de8-e5c2-4055-958b-f604c6a40cf1'
AND uf.OrganizationID='e5750df1-0409-46b6-9aba-7f07be7c890c'


enter image description here


After Distinct (113 records). totalRows should be 113 only since what I'm getting are distinct records.


SELECT distinct
uf.OrigFileName,
uf.CreatedOn,
sdiTran.Status,
sdiFS.FileName,
sdiFile.ArchiveLogID,
COUNT(*) over() as totalRows
FROM [SDI].dbo.UploadedFile uf
inner join [SDI].dbo.SDIFile sdiFile on uf.UploadedFullFileName = sdiFile.OriginalName
left join [SDI].dbo.SDITransaction sdiTran on sdiFile.ID = sdiTran.SDIFileID
inner join [SDI].dbo.SDIFSArchive sdiFS on sdiFile.ID = sdiFS.SDIFileID
WHERE uf.CommunityID = '7cc67de8-e5c2-4055-958b-f604c6a40cf1'
AND uf.OrganizationID='e5750df1-0409-46b6-9aba-7f07be7c890c'


enter image description here

Kim Kim
Answer

You can use the distinct in a sub query:

SELECT *
    , COUNT(*) over() as totalRows
FROM (
    SELECT DISTINCT
    uf.OrigFileName,
    uf.CreatedOn,
    sdiTran.Status,
    sdiFS.FileName,
    sdiFile.ArchiveLogID
    FROM [SDI].dbo.UploadedFile uf 
    inner join [SDI].dbo.SDIFile sdiFile on uf.UploadedFullFileName = sdiFile.OriginalName 
    left join [SDI].dbo.SDITransaction sdiTran on sdiFile.ID = sdiTran.SDIFileID 
    inner join [SDI].dbo.SDIFSArchive sdiFS on sdiFile.ID = sdiFS.SDIFileID
    WHERE uf.CommunityID = '7cc67de8-e5c2-4055-958b-f604c6a40cf1' 
    AND uf.OrganizationID='e5750df1-0409-46b6-9aba-7f07be7c890c'
) A