April 10, 2020 at 9:37 am
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
April 10, 2020 at 3:12 pm
TOP 10 based on what metric? What are you ordering by?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 10, 2020 at 7:30 pm
Something like this maybe
;with
SiteInfo(SiteId, SiteName, JobId, Grade) as (
select 1, 'aaa', 1, 'a'
union all
select 1, 'aaa', 1, 'a'
union all
select 1, 'aaa', 1, 'b'
union all
select 1, 'aaa', 2, 'a'
union all
select 1, 'aaa', 2, 'b'
union all
select 1, 'aaa', 2, 'c'
union all
select 3, 'abc', 3, 'a'
union all
select 3, 'abc', 3, 'a'
union all
select 3, 'abc', 3, 'b'
union all
select 3, 'abc', 2, 'a'
union all
select 3, 'abc', 2, 'b'
),
unq_cte(SiteId, SiteName, JobId, Grade) as (
select distinct
SiteId,
SiteName,
JobId,
Grade
from
SiteInfo),
site_cte(SiteId, SiteName, JobGrade_Count) as (
select
SiteId,
SiteName,
count(*)
from
unq_cte
group by
SiteId,
SiteName)
select top(10)
SiteId,
SiteName,
JobGrade_Count,
row_number() over (order by JobGrade_Count desc) Row_Num,
dense_rank() over (order by JobGrade_Count desc) Row_Rank
from
site_cte
order by
JobGrade_Count desc;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply