Last post Dec 12, 2011 09:21 AM by Swati Annaldas
Dec 12, 2011 06:19 AM|Swati Annaldas|LINK
When i execute the following query i get the following error:
Column 'COM_Owners.Id_Owner' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
where i am doing wrong..Please help..its urgent
SELECT * From ( SELECT OWNR.Id_Owner ,OWNR.Owner_Name ,USR.Id_Users ,ORD.Customer_Id ,USR.First_Name + ' ' + USR.Last_Name AS Name ,USR.User_Role_Id ,ISNULL(COUNT(ID_Order),0) TotalCount -- TOTAL ORDERS -- ,ISNULL(SUM(ORDDG.Stitch_Count) / COUNT(ORD.Id_Order),0) AvgStitchCount -- AVERAGE STICHCOUNT -- ,(ISNULL(SUM(Revision_Version) / COUNT(Id_Order),0)) AverageRevision -- AVERAGE REVISION -- FROM USR_Users USR LEFT JOIN COM_Owners OWNR ON USR.Owner_Id = OWNR.Id_Owner LEFT JOIN ORD_Orders ORD ON ORD.Customer_Id = USR.Id_Users LEFT JOIN ORD_Order_Details ORD_DETAILS ON ORD.Id_Order = ORD_DETAILS.Order_Id LEFT JOIN Ord_Digitize ORDDG ON ORD.Id_Order = ORDDG.Order_Id WHERE USR.IsDeleted = 0 AND ORD.Id_Order = ORD_DETAILS.Order_Id AND USR.Owner_Id !=5 OR USR.Owner_Id = NULL OR USR.Owner_Id IS NULL AND USR.User_Role_Id IN (8,10) AND USR.Id_Users = NULL OR USR.Id_Users IS NULL AND ORD_DETAILS.Order_Date BETWEEN '03-May-2011' AND '08-Dec-2011' ) AS TEMP GROUP BY Id_Owner ,Owner_Name ,Id_Users ,Customer_Id ,Name ,User_Role_Id ,TotalCount ,AverageRevision ,AvgStitchCount --,TotalRevision HAVING TotalCount > 0 OR AverageRevision > 0 OR AvgStitchCount > 0 ORDER BY CASE 3 WHEN 0 THEN TotalCount WHEN 3 THEN AvgStitchCount WHEN 4 THEN AverageRevision END, CASE 0 WHEN 0 THEN ' ASC' ELSE ' DESC ' END
Dec 12, 2011 08:03 AM|kuber.manral|LINK
you have to use any Aggregate Function(like MAX,MIN..) in your select list, if you are grouping the particular Columns. For example, 'Id_Owner'. Because you are Grouping on its basis, in Select list, you have to use ant aggregate Function.
Dec 12, 2011 09:10 AM|Swati Annaldas|LINK
yup..i know that, i want my query to return list of values but not just one record.. i have tried with MIN aggregate but it returns only one record..what aggregate function i need to use..?
Dec 12, 2011 09:13 AM|kuber.manral|LINK
You should not group your query with so many column values. Try to put only those columns which are actually needed to Group data in a particular manner.
Dec 12, 2011 09:21 AM|Swati Annaldas|LINK
yup..i agree with you..but its my requirement..any suggestion pls..??