4

i need to select two columns.1. calculate sum of one column and display it 2.display column as it is. so i tried below code

SELECT Sum(CONVERT(FLOAT, Replace(total, Char(0), ''))) AS Total, 
       [product name] 
FROM   tb_sales_entry_each_product 
GROUP  BY [sales date]

error message

Column 'tb_sales_entry_each_product.Product Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

where i made error.thanks

Hawk
  • 5,060
  • 12
  • 49
  • 74
Happy
  • 1,105
  • 5
  • 17
  • 25
  • Your original query had one column in select statement and a different column in group by clause. What you needed to change was to put column from select statement into group by clause –  Jan 15 '14 at 21:20

2 Answers2

1

Try this:

select SUM(CONVERT(float, REPLACE(Total, CHAR(0), ''))) as Total, 
[Product Name]  ,[Sales Date]
from tb_sales_entry_each_product 
group by [Sales Date],[Product Name]
  • @Happy Your column product name was missing in group by. Basic funda is that if you use any column with any aggregate function in select, you need to mention it in group by, that it was mentioned in the error "because it is not contained in either an aggregate function or the GROUP BY clause." –  Jan 15 '14 at 10:04
1

just need to group

select SUM(CONVERT(float, REPLACE(Total, CHAR(0), ''))) as Total, [Product Name]  
from tb_sales_entry_each_product group by [Sales Date], [product name]

When ever you do a numercial count sum etc, any other columns need to be grouped. thats all your missing

lemunk
  • 2,616
  • 12
  • 57
  • 87
  • already did , if any columns exist in your where clause when performing a numerical function, you need to include those columns as well into the group by clause – lemunk Jan 15 '14 at 10:08
  • oh if its still confusin, check .. http://stackoverflow.com/questions/13998552/why-do-we-need-group-by-with-aggregate-functions may help – lemunk Jan 15 '14 at 10:15