Group by Clause Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group by Clause Error

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-16 : 07:29:30

I have a query to generate a raw data but when running this query i got an error that i have an invalid field that not in a select group statement. kindly please help me to find this error. thank you.

error message

Msg 8120,Level 16,State 1, Line 4
Column'finphones.asuprodlineid'is invalid in the select list because it isnot contained in either an aggregatefunctionor the GROUPBY clause.

SELECT

		finphones.asuprodlineid  
		,finphones.itemid  
		,finphones.finishedfone AS finishedfone
		,ISNULL(MAX(itconsump.asuItemVerId),MAX(itbom.asuItemVerId))AS PartNo
		,ISNULL(MAX(itconsump.itemname),MAX(itbom.itemname))AS ItemName
		,ISNULL(CAST(consumptionTable.RECLAIMQTY ASNUMERIC),0)AS RECLAIMQTY  
FROM (
Select *
From
(
select prodList.ASUPRODLINEID, prodlist.itemid, bomlist.partQty as requiredQty, bomlist.partId as requiredPart from
(
	select * 
		from #PRODORDERS pt  
) prodList  
leftouterjoin
(
Select *
   
from	bomversion bv  
innerjoin
        bom on bom.dataareaid = bv.dataareaid and bom.bomid = bv.bomid   
where
        bv.dataareaid='tdk'
and bom.dataareaid='tdk'
AND bv.name ='Nornal'
and bom.bomqty > 0  
)bomList on bomList.phoneId = prodList.itemid   
)x --group by x.ASUPRODLINEID, x.itemid, x.requiredPart 
) bomTable 

FULLJOIN
(
SELECT
	 pt.asuprodlineid  
		,partNo = it.asuItemVerId
		,pt.itemid  
		,RECLAIMQTY =CASEWHEN (ivg.asuReclaim = 1)THEN pjb.bomconsump ELSE 0 END
FROM   prodjournalbom pjb 
INNERJOIN #PRODORDERS pt ON pt.prodid = pjb.prodid
INNERJOIN(SELECT DATAAREAID   
				,itemid  
				,inventtransid  
				-- ,sum(costamountposted) as costamountposted  
				-- ,sum(costamountadjustment) as costamountadjustment  
				,costamountposted
				,costamountadjustment
				FROM  dbo.inventtrans 
				WHERE dataareaid ='tdk') ivt--@dataArea  
				--GROUP BY DATAAREAID  
				--  ,inventtransid  
				--  ,itemid)ivt 
				ON pjb.DATAAREAID = ivt.DATAAREAID 
								and pjb.itemid = ivt.itemid 
								AND pjb.inventtransid = ivt.inventtransid  
INNERJOIN
    dbo.inventtable it ON it.dataareaid ='tdk'AND ivt.itemid = it.itemid  
INNERJOIN
    dbo.inventitemgroup ivg on ivg.dataareaid = it.dataareaid AND ivg.itemgroupid = it.itemgroupid  
WHERE
    pjb.dataareaid ='tdk'
--GROUP BY   
-- pt.asuprodlineid, pt.itemid, pjb.itemid, it.asuitemverid, it.itemgroupid, ivg.asureclaim  
)consumptionTable ON bomTable.requiredPart = consumptionTable.partNo 
					AND bomTable.itemID = consumptionTable.itemID
					and bomTable.ASUPRODLINEID = consumptionTable.ASUPRODLINEID
					--AND bomTable.MONTHDESCRIPTION=consumptionTable.MONTHDESCRIPTION
					
JOIN
(--select asuprodlineid, itemid,  COUNT(distinct pt.prodid) as finishedfone 
select asuprodlineid, itemid, pt.prodid as finishedfone
from #PRODORDERS pt  
--group by asuprodlineid, itemid        
) finphones on (finphones.ASUPRODLINEID = bomtable.ASUPRODLINEID
				AND finphones.itemid = bomtable.ItemID)
				OR(finphones.asuprodlineid = consumptionTable.asuprodlineid
					AND finphones.itemid = consumptionTable.itemid)
LEFTJOIN
      dbo.inventtable itbom ON itbom.dataareaid ='tdk'AND itbom.itemid = bomtable.requiredPart
LEFTJOIN
	  dbo.inventitemgroup ivtbom ON ivtbom.dataareaid = itbom.dataareaid AND ivtbom.itemgroupid = itbom.itemgroupid
LEFTJOIN
      dbo.inventtable itconsump ON itconsump.dataareaid ='tdk'AND itconsump.itemid = consumptionTable.PartNo
LEFTJOIN
	  dbo.inventitemgroup ivtconsump ON ivtconsump.dataareaid = itconsump.dataareaid AND ivtconsump.itemgroupid = itconsump.itemgroupid
	
--  GROUP BY   
-- finphones.asuprodlineid, finphones.itemid, finphones.finishedfone, itconsump.asuItemVerId, bomtable.requiredPart, bomTable.requiredQty  

--ORDER BY   
-- finphones.ASUPRODLINEID, finphones.ITEMID, itconsump.asuItemVerId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-16 : 10:15:14

You need to GROUP BY on the columns to be used directly in the SELECT list if you're applying aggregates over some of the other columns.
Uncomment your GROUP BY and it should work fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-16 : 22:02:47

actually i dont need them to group by or aggregate that's why i place uncomment.
how can i put this finphones.asuprodlineid as not aggregate value in the select statement.

I think this is the portion where i got an error.

JOIN
(--select asuprodlineid, itemid,  COUNT(distinct pt.prodid) as finishedfone 
select asuprodlineid, itemid, pt.prodid as finishedfone
from #PRODORDERS pt  
--group by asuprodlineid, itemid        
) finphones on (finphones.ASUPRODLINEID = bomtable.ASUPRODLINEID
				AND finphones.itemid = bomtable.ItemID)
				OR(finphones.asuprodlineid = consumptionTable.asuprodlineid
					AND finphones.itemid = consumptionTable.itemid)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 11:47:22

As per this you need to include all the fields that you need to join with in GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-18 : 20:21:49

Thanks Visakh. Just add another field to get the desired grouping of records.

Btw, I tried to join the result of CTE from other table but there are duplicate Prodid that's why I use row_number.
Why this row_number did not work? The result of RN is all 1. I need to get the latest REASON based on REQUESTEDDATETIME using PRODID as reference.

[code]
With CTE AS
(Select ????
)
SELECT arpl.REASON, arpt.REQUESTEDDATETIME, DAT.PRODID, dat.ASUPRODLINEID, dat.ITEMID, dat.PartNo, dat.finishedfone, dat.ItemName,
dat.REFERENCEBOMQTY, dat.requiredQty, dat.NEWPARTAVGCOST, dat.REFURBPARTAVGCOST,
CORESQTY=(CASE When dat.requiredQty>dat.Total Then dat.RequiredQty-dat.Total else 0 End), dat.NEWPARTSQTY, dat.RECLAIMQTY, dat.REFURBQTY, dat.TOTAL,
rn=(Select ROW_NUMBER() OVER (PARTITION BY dat.prodid ORDER BY arpt.REQUESTEDDATETIME DESC))
FROM CTE dat
Left Outer Join dbo.ASUREQUESTPARTTABLE arpt
ON arpt.PRODUCTIONREF=dat.PRODID and arpt.DATAAREAID='tcp'
Left Outer Join dbo.ASUREQUESTPARTLINE arpl
ON arpl.REQUESTID=arpt.REQUESTID --and arpl.ITEMVERID=dat.PartNo and arpl.DATAAREAID='tcp'
WHERE dat.PartNo='HTC1823'
Order by dat.PRODID[/cide]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-18 : 21:03:26

It's working. Wrong Syntax in row_number.

rn=(SELECT ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc)

Correct:
rn=ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 01:51:08

Oh ok..I prefer putting aliases using AS

ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc) AS Rn,..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources