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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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