SQL Server - select command

Asked By vicky nehru on 10-Nov-10 01:18 AM

I am writing a query in sql server 2005 for select command it produced error, How to solve the error. . .

see the query and error below. . .

Query :

select ItemCode,ItemDescription,sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt'

Error :

Msg 8120, Level 16, State 1, Line 4
Column 'SalesStockRM.ItemCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Give me a solutions. . .
Reena Jain replied to vicky nehru on 10-Nov-10 01:29 AM
hi,

since you  are sum function which will return a single row and itemcode will return multiple rows so either use this query
select sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt'
or
select ItemCode,ItemDescription,sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt' group by ItemCode,ItemDescription

hope this will help you
Nowshad M replied to vicky nehru on 10-Nov-10 01:30 AM
Try this..

select ItemCode,ItemDescription,sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt' Group by [ItemCode]
Sagar P replied to vicky nehru on 10-Nov-10 01:32 AM
This error message appears when you try to use a column in the SELECT list that does not appear in an aggregate function or in the GROUP BY clause.

Errors of the Severity Level 16 are generated by the user and are corrigible by the user. The statement cannot be executed this way. You must either use this column in an aggregate function or add it to the GROUP BY clause.

So you can try something like this;

select ItemCode,ItemDescription,sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt' GroupBy ItemCode,ItemDescription

However, you need to verify on the logical level if they return the expected resultset.

Add group by clause a the end of your select query
Lalitha Kumaran replied to vicky nehru on 10-Nov-10 02:45 AM

Solution :

Add Group by Clause at the end of your select query.

        select ItemCode,ItemDescription,sum(S36) as [36],sum(S38) as [38],sum(S39) as [39] from SalesStockRM  where      
       DayDate>='09/11/2010' and DayDate<='11/11/2010' and Pieces='Shirt'    group by ItemCode,ItemDescription

      Hope the Below syntax and example helps you to understand the Group by Clause.

Syntax of Group By Clause:

SELECT columns FROM table [WHERE search_condition]   GROUP BY grouping_columns    [HAVING search_condition]
        [ORDER BY sort_columns]

Example:

SELECT OrderID, COUNT(ProductID) AS NumberOfOrders
FROM   Order_Details
GROUP BY OrderID

OrderID   NumberOfOrders
10248         3
10249         2


Abi naya replied to Lalitha Kumaran on 10-Nov-10 05:09 AM
Adding Group by will help you. Check it out whether you are getting the required result.

The RULE to be followed with SELECT statement is:
Whenever you write a select statement with aggregate function you should use either group by or order by(depending on the logic you use). All the fields present in the select query should occur in the GROUP BY clause or else it will throw not a group by exception.