Top 10, count Distinct of multiple columns in select statement

Transact-SQL
, ,
1

Hi All

select TOP 10 SiteId, SiteName, count (distinct (JobID,Grade)) from SiteInfo

I need to count unique value of jobID, Grade and get the count from each site, then highlight only the TOP 10 site. JobID 112 can belongs to Grade A, B also can be repeated many times so i need to make a distinct of both to get the unique count. if 112 with grade A repeated 10 times then count is one, 112 with Grade B is repeated many times then it should count as one. Can someone help me to write the query

Thanks
shagil

2

hi

hope this helps !! :slight_smile:

Please click arrow to the left for Sample Data

drop create sample data 
DROP TABLE #SiteInfo 
GO 

CREATE TABLE #SiteInfo
(
SiteId INT 
,SiteName varchar(100)
, JobId INT
, Grade VARCHAR(5)
)
GO 

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   1     -- SiteId - int
     , 'ok'   -- SiteName - varchar(100)
     , 123    -- JobId - int
     , 'A'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   1     -- SiteId - int
     , 'ok'   -- SiteName - varchar(100)
     , 234    -- JobId - int
     , 'B'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   1     -- SiteId - int
     , 'ok'   -- SiteName - varchar(100)
     , 234    -- JobId - int
     , 'A'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   1     -- SiteId - int
     , 'ok'   -- SiteName - varchar(100)
     , 123    -- JobId - int
     , 'B'   -- Grade - varchar(5)
    )

GO 
-----
INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   2     -- SiteId - int
     , 'de'   -- SiteName - varchar(100)
     , 777    -- JobId - int
     , 'A'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   2     -- SiteId - int
     , 'de'   -- SiteName - varchar(100)
     , 111    -- JobId - int
     , 'B'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   2    -- SiteId - int
     , 'de'   -- SiteName - varchar(100)
     , 777    -- JobId - int
     , 'A'   -- Grade - varchar(5)
    )

INSERT INTO [#SiteInfo]
     (
         [SiteId]
         , [SiteName]
         , [JobId]
         , [Grade]
     )
VALUES
    (   2     -- SiteId - int
     , 'de'   -- SiteName - varchar(100)
     , 87    -- JobId - int
     , 'B'   -- Grade - varchar(5)
    )

GO

Please click arrow to the left for SQL

SQL 
; WITH cte AS 
(
select   DISTINCT siteid,sitename,  CAST(JobID AS VARCHAR) +Grade  AS ok FROM #SiteInfo
) , cte_1 AS 
(
SELECT siteid , sitename, COUNT(ok)  AS cntok FROM cte  GROUP BY siteid ,sitename
) 
SELECT  TOP 1 'SQL Output TOP 1 ',  *  FROM cte_1 ORDER BY cntok DESC 
GO

image

2 Likes
3

This is a little simpler than the CTE

Select siteid,sitename, count(Distinct(concat(JobID, Grade))) as cntok from #SiteInfo
group by siteid,sitename
order by siteid,sitename
2 Likes
4

Definitely
Simpler

Thanks,
Mike
:+1::+1:

1 Like
5

Thank you so much, brilliant logic by using concat and then using distinct on it...

6

Thanks for your help