SQL Group By Throwing Up Error (SQL Server)

Question

I have SQL code that throws up an error saying

Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=WONUM

The code works fine until I add the group by:

  select *
  from workorder 
  left join labtrans on  labtrans.refwo=workorder.wonum and labtrans.siteid=workorder.siteid
  left join matusetrans on workorder.wonum=matusetrans.refwo and workorder.siteid=matusetrans.tositeid  and linetype not in (select value from synonymdomain where domainid='LINETYPE' and maxvalue='TOOL')
  left join  locations  on locations.location = workorder.location and locations.siteid=workorder.siteid
  left join person on personid in (select personid from labor where laborcode = labtrans.laborcode)
  left join po on workorder.wonum=po.hflwonum and workorder.siteid=po.siteid and workorder.orgid=po.orgid
  left join companies on companies.company = po.vendor and companies.orgid=po.orgid
  left join pluspcustomer on pluspcustomer.customer=workorder.pluspcustomer
  where workorder.wonum='10192'
  group by personid
 ;

You can't select * and group by personid. And your error message doesn't look like sql server. Which select @@version are you using? — SqlZim
What don't you understand? You have select * and group by personid. You have a zillion columns that are not in the group by and are not in aggregation functions. — Gordon Linoff
without any aggregate function you cannot just randomly include group by in the last. — Dheeraj Sharma
The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh

LONG LONG · Accepted Answer · 2017-04-04T13:41:18

if you only GROUP BY personid, you cannot select everything except personid, OR the fields used by aggregate functions such as SUM,MAX, etc

UPDATE

If you just want to see the duplicate personid, you could use:

 select personid 
 from table 
 group by personid

But be careful here: If you write query like this, the only field that to determine the duplicate records is persionid, if you need to uniquely identify each persionid from different CompanyId, you need to group by persionid, CompanyId, otherwise, same personId from different company will be considered as the duplicate records.

But if you want to delete those duplicate records, you should use ROW_NUMBER()OVER (Partition by persionid Order by your_criteria) to delete the duplicate records. Try to do some searches to see how does that work, usually I prefer to use that function along with the CTE table expression.

SQL Group By Throwing Up Error (SQL Server)

2 Answers