Navy Navy - 7 months ago 8
SQL Question

Column is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

I am getting an error on a

SELECT
query when I am trying to use
ORDER BY
clause. When I remove
ORDER BY
it works fine.

Here is my query:

Select tbl_User.CompanyName as CompanyName,
tbl_User.ResellerName as ResellerName,
tbl_New_Web_Amc.Domain_Name,
tbl_Website_Type.Website_type as [Type],
convert(varchar,Web_Amc_Start_Date,106) as StartDate,
convert(varchar,Web_Amc_End_Date,106) as EndDate,
tbl_New_Web_Amc.Web_Amc_Amount as Amount,
tbl_Billing.CompanyName as BillingCompany,
tbl_Billing.ContactPerson1,
tbl_Billing.Mobile1,
tbl_Billing.ContactPerson2,
tbl_Billing.Mobile2,
tbl_Billing.ContactPerson3,
tbl_Billing.Mobile3,
tbl_Billing.Telephone1,
tbl_Billing.Telephone2,
tbl_Billing.Telephone3,
tbl_Billing.EmailId1,
tbl_Billing.EmailId2,
tbl_Billing.EmailId3
from tbl_New_Web_Amc
join tbl_User on tbl_New_Web_Amc.Customer_Id=tbl_User.UserId
join tbl_Domain_Details on tbl_New_Web_Amc.Customer_Id=tbl_Domain_Details.Customer_Id
join tbl_Billing on tbl_New_Web_Amc.Customer_Id=tbl_Billing.User_ID
join tbl_Website_Type on tbl_Website_Type.Type_Id=tbl_New_Web_Amc.Web_Amc_Site_Type
where WebsiteAmc_Id is not null
group by tbl_User.CompanyName ,
tbl_User.ResellerName ,
tbl_New_Web_Amc.Domain_Name,
tbl_Website_Type.Website_type,
convert(varchar,Web_Amc_End_Date,106),
convert(varchar,Web_Amc_Start_Date,106),
tbl_New_Web_Amc.Web_Amc_Amount,
tbl_Billing.CompanyName,
tbl_Billing.ContactPerson1,
tbl_Billing.ContactPerson2,
tbl_Billing.ContactPerson3,
tbl_Billing.Telephone1,
tbl_Billing.Telephone2,
tbl_Billing.Telephone3,
tbl_Billing.Mobile1,
tbl_Billing.Mobile2,
tbl_Billing.Mobile3,
tbl_Billing.EmailId1,
tbl_Billing.EmailId2,
tbl_Billing.EmailId3
order by convert(datetime,Web_Amc_End_Date,106)


Getting the following Error


Column "tbl_New_Web_Amc.Web_Amc_End_Date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.


Where I am going wrong. How to rectify it?? Please help.

Answer

ORDER BY command requires the exact column to be present in the select query as well, since as per the order in SQL Server the order by command is executed after the SELECT command. From what I can gather in your query, you have used:

convert(varchar,Web_Amc_End_Date,106)

in the select clause, and

convert(datetime,Web_Amc_End_Date,106)

in the order by clause.

Consider changing them to the same format, your query will work