CODEWITHSUNDEEP
sqlsql-server
santhosha
santhosha

Reputation: 377

Group by function not working in sql server?

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

Upvotes: 0

Views: 76

Answers (2)

Kevin Hogg
Kevin Hogg

Reputation: 1781

If you use a column in a GROUP BY you typically don't use an aggregrate function (MIN, SUM, etc.) on the same column in the SELECT.

Looking at the K_HM_MasterHatcherDet table in isolation, we can see the following query (based on the original):

 SELECT MIN(MH.sno) AS hatchid,
        CONVERT(varchar, MH.pulloutdate, 103) as Pulloutdate,
        SUM(MH.ChicksQnty) as Noofchicks,
        SUM(MH.culls) as culls,
        SUM(MH.Chicksqnty - MH.culls) as Salablechicks 
   FROM K_HM_MasterHatcherdet AS MH
  WHERE MH.pulloutdate IS NOT NULL
  GROUP BY MH.sno, MH.pulloutdate, MH.Chicksqnty, MH.culls

The problem here is the GROUP BY will return each distinct MH.sno, MH.PulloutDate, MH.Hicksqnty, MH.Culls so something like MIN(MH.sno) doesn't achieve anything.

The best approach to take is to first get all records on an individual basis like below:

 SELECT MH.sno AS hatchid,
        CONVERT(varchar, MH.pulloutdate, 103) as Pulloutdate,
        MH.ChicksQnty as Noofchicks,
        MH.culls as culls,
        (MH.Chicksqnty - MH.culls) as Salablechicks 
   FROM K_HM_MasterHatcherdet AS MH
  WHERE MH.pulloutdate IS NOT NULL

And then and build up the GROUP BY and aggregate functions from that point.

Below I get the totals for each HatchId, and PulloutDate:

 SELECT MH.sno AS hatchid,
        CONVERT(varchar, MH.pulloutdate, 103) as Pulloutdate,
        SUM(MH.ChicksQnty) as Noofchicks,
        SUM(MH.culls) as culls,
        SUM(MH.Chicksqnty - MH.culls) as Salablechicks 
   FROM K_HM_MasterHatcherdet AS MH
  WHERE MH.pulloutdate IS NOT NULL
  GROUP BY MH.sno, MH.PulloutDate

The main thing here is the GROUP BY handles MH.sno and MH.PulloutDate, whilst the aggregate functions operate on the remaining columns in the query.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

This is a formatted comment. Start with this query:

select min(MH.sno) as hatchid
,(SH.name+'-'+SH1.name) as hatcheryname

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

If it does not run successfully, edit it until it does. Then add your other aggregates and run it again. It should be ok till now.

Next, add the other fields to both your select and group by clauses, one by one. Run the query each time to make sure it's ok. Deal with errors as they come up. Eventually, you will have a working query.

Upvotes: 1

Related Questions