按功能分组在sql server中无法正常工作？ [英] Group by function not working properly in sql server?

问题描述

 选择 min（MH.sno） as  hatchid，（ SH.name + '   -   + SH1.name） as  hatcheryname， convert （ varchar ，SD.settingdate， 103 ） as  settingdate，
 sum（SD.Recievedeggs） as  eggsdispatchqty，sum（SD.quantity） as  settingqty， convert （ varchar ，MH.pulloutdate， 103 ） as  Pulloutdate，SD.batchno  as  flockno，
 sum（MH.ChicksQnty） as  Noofchicks，sum（MH.culls） as  culls，cast（（round（（（（MH .Chicks Qnty-MH.culls）/SD.quantity）* 100）， 2 ）） as   float ） as  hatchpercent，
 sum（cast（round（（（SD.quantity * 87）/ 100 ）， 0 ） as   int ））  as  ActSalablechicks，sum（（MH.Chicksqnty- MH.culls）） as  Salablechicks  from  K_HM_SetterGetterAllocationDet SD 
  inner   join  K_HM_SetterDetails MS  on  SD.sno = MS.ID 
  inner   join  K_HM_HatcherDetails HD  on  MS.sno = HD.ID 
  inner   join  K_HM_MasterHatcherd et MH  on  HD.Sno = MH.ID 
  inner   join  K_HM_GetterSetterDet SH   SD.name = SH.sno 
  inner   join  K_HM_GetterSetterDet SH1   SH1.sno = HD.hatchername 其中 HD.attrited = '  true' 
 和 MH.pulloutdate   不  null  和 MS.pulloutdate   不  null   group   by  
 MH.sno，SH.name，SH1.name，SD.settingdate，MH.pu lloutdate，SD.batchno，
 MH.Chicksqnty，MH.culls，SD.quantity  order   by  settingdate  desc  

我有四个不同的表数据，这四个表有一些关系。实际上我想要的是取决于设置列，列的一些总和（列）。我试过这样但是它显示的是不显示行总和的不同行。我怎么写请帮助我。在此先感谢

解决方案

尝试以这种方式进行更改：

  SELECT  min（MH.sno） as  hatchid，（SH.name + '   - ' + SH1.name） as  hatcheryname， convert （ varchar ，SD.settingdate， 103 ） as  settingdate，
 sum（SD.Recievedeggs） as  eggsdispatchqty，sum（SD.quantity） as  settingqty， convert （ varchar ，MH.pulloutdate， 103 ） as  Pulloutdate，SD.batchno  as  flockno，
 sum（MH.ChicksQnty） as  Noofchicks，sum（MH.culls） as  culls，cast（（round（（（MH.ChicksQnty- MH.culls）/SD.quantity ）* 100）， 2 ）） as   float ） as  hatchpercent，
 sum（cast（round（（（SD.quantity * 87）/ 100）， 0 ） as   int ）） as  ActSalablechicks，sum（（MH.Chicksqnty- MH.culls）） as  Salablechicks 来自 K_HM_SetterGetterAllocationDet SD 
  inner   join  K_HM_SetterDetails MS  on  SD.sno = MS.ID 
  inner   join  K_HM_HatcherDetails HD >  MS.sno = HD.ID 
 内部 加入 K_HM_MasterHatcherdet MH   HD.Sno = MH.ID 
  inner   join  K_HM_GetterSetterDet SH  on  SD.name = SH.sno 
  inner   join  K_HM_GetterSetterDet SH1  SH1.sno = HD.hatchername 其中 HD.attrited = '的类=code-keyword>   true' 
 和 MH.pulloutdate   不  null  和 MS.pulloutdate   不  null  
  GROUP   BY （SH.name + '   - ' + SH1.name）， convert （ varchar ，SD.settingdate，  103 ）， convert （ varchar ，MH .pulloutdate， 103 ），SD.batchno，
  ORDER   BY   CONVERT （ DATETIME ， CONVERT （ varchar ，SD.settingdate， 103 ）） DESC  

select min(MH.sno) as hatchid,(SH.name+'-'+SH1.name) as    hatcheryname, convert(varchar,SD.settingdate,103)as settingdate,
     sum(SD.Recievedeggs) as eggsdispatchqty, sum(SD.quantity) as   settingqty, convert(varchar,MH.pulloutdate,103) as Pulloutdate, SD.batchno as flockno,
    sum(MH.ChicksQnty) as Noofchicks, sum(MH.culls) as culls, cast((round((((MH.ChicksQnty-  MH.culls)/SD.quantity)*100),2)) as float) as hatchpercent, 
    sum(cast(round(((SD.quantity*87)/100),0)as int)) as ActSalablechicks, sum((MH.Chicksqnty-  MH.culls))as Salablechicks from K_HM_SetterGetterAllocationDet SD
     inner join K_HM_SetterDetails MS on SD.sno=MS.ID
     inner join K_HM_HatcherDetails HD on MS.sno=HD.ID
     inner join K_HM_MasterHatcherdet MH on HD.Sno=MH.ID
     inner join K_HM_GetterSetterDet SH on SD.name=SH.sno
     inner join K_HM_GetterSetterDet SH1 on SH1.sno=HD.hatchername  where    HD.attrited='true'
     and MH.pulloutdate is not null and MS.pulloutdate is not null  group by
     MH.sno,SH.name,SH1.name,SD.settingdate,MH.pulloutdate,SD.batchno,
     MH.Chicksqnty,MH.culls,SD.quantity order by settingdate desc

I have four different tables data,these four tables have some relation one to one.Actually what i want is depending settingdate column,some sum(column) of columns . I have tried like this but it is showing different rows not showing sum of row. How can i write please help me out. Thanks in advance

解决方案

Try to change it this way:

SELECT min(MH.sno) as hatchid,(SH.name+'-'+SH1.name) as    hatcheryname, convert(varchar,SD.settingdate,103)as settingdate,
     sum(SD.Recievedeggs) as eggsdispatchqty, sum(SD.quantity) as   settingqty, convert(varchar,MH.pulloutdate,103) as Pulloutdate, SD.batchno as flockno,
    sum(MH.ChicksQnty) as Noofchicks, sum(MH.culls) as culls, cast((round((((MH.ChicksQnty-  MH.culls)/SD.quantity)*100),2)) as float) as hatchpercent,
    sum(cast(round(((SD.quantity*87)/100),0)as int)) as ActSalablechicks, sum((MH.Chicksqnty-  MH.culls))as Salablechicks from K_HM_SetterGetterAllocationDet SD
     inner join K_HM_SetterDetails MS on SD.sno=MS.ID
     inner join K_HM_HatcherDetails HD on MS.sno=HD.ID
     inner join K_HM_MasterHatcherdet MH on HD.Sno=MH.ID
     inner join K_HM_GetterSetterDet SH on SD.name=SH.sno
     inner join K_HM_GetterSetterDet SH1 on SH1.sno=HD.hatchername  where    HD.attrited='true'
     and MH.pulloutdate is not null and MS.pulloutdate is not null
GROUP BY (SH.name+'-'+SH1.name), convert(varchar,SD.settingdate,103), convert(varchar,MH.pulloutdate,103), SD.batchno,
ORDER BY CONVERT(DATETIME, CONVERT(varchar,SD.settingdate,103)) DESC

这篇关于按功能分组在sql server中无法正常工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！