SQL join giving duplicate rows with group by clause

问题: Thanks for all help i am facing issue while using joins Using one sccm query select count(distinct resourceid) as ModelCount,caption0,CSDVersion0 from [dbo].[v_GS...

问题:

Thanks for all help i am facing issue while using joins

Using one sccm query

    select count(distinct resourceid) as ModelCount,caption0,CSDVersion0 from [dbo].[v_GS_OPERATING_SYSTEM]  
where caption0 like '%Windows Server%'
group by caption0,CSDVersion0

Data iam getting is as following

enter image description here

so iam getting following data, and i have made a another table with extended support dates for every windows version

select * from datesinfo

data iam getting

enter image description here

after doing join on both tables iam writing query this

    select count(distinct OS.resourceid) as ModelCount,(dt.serverinfo),CSDVersion0,dt.Extendedsupportenddate,dt.servicepacksupportenddate from [dbo].[v_GS_OPERATING_SYSTEM] OS
inner join datesinfo dt on  os.caption0=dt.serverinfo where os.caption0 like '%Windows Server%'
group by OS.caption0,OS.CSDVersion0,dt.serverinfo,dt.Extendedsupportenddate,dt.servicepacksupportenddate

iam getting one row duplicate with 672 rows, can you please explain why all data is distinct

enter image description here


回答1:

You are grouping by OS.caption0 which is not included in your select statement.
I think that if you include this column then the 2 rows will not be duplicates.
Or maybe you don't want to group by this column so remove it from group by.


回答2:

In datesinfo, you have record 3 and 5 that have the same name, with different servicepacksupportenddate. Each appears once in your final result, this is normal.

  • 发表于 2019-02-24 22:36
  • 阅读 ( 189 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除