Msg 8120 and creating new table

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

General SQL Server Forums

New to SQL Server Programming

Msg 8120 and creating new table

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-03-25 : 16:49:52

Here is my code:

SELECTAVG(ListPrice)ASavrg,
ProductLine,
SellStartDate
FROMProduction.Product

Here is the error message:

Msg 8120, Level 16, State 1, Line 13

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

1. What am I doing wrong?
2. Also, I created a new table, in SQL Express but I can't find it in the tables folder. Where did it save to?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-25 : 16:53:36

With that query you are calculating the average of each Product line for each SellStartDate. List both those columns in the group by clause:

SELECTAVG(ListPrice)ASavrg,
ProductLine,
SellStartDate
FROMProduction.Product
GROUP BY 
   ProductLine,
   SellStartDate

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-03-25 : 17:01:53

I didn't want to Average by group. I just wanted to list out 3 columns (ListPrice, Product Line, Sell Start Date). Then I wanted to take the average of only the List Price values. That average value would be in an alias column called 'avrg.'

Is this possible?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-25 : 17:20:11

See if this is what you are looking for:

SELECT 
   ListPrice,
   AVG(ListPrice) OVER() AS avrg,
   ProductLine,
   SellStartDate
FROM Production.Product

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-25 : 17:24:13

Probably possible, but I'm not sure I unerstand what you want see for results. Here are some links on how to post a question. If you check them out and supply DDL, DML and expected output, it makes it much easier for us to help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-03-26 : 16:36:50

quote:
Originally posted by James K

See if this is what you are looking for:
SELECT
ListPrice,
AVG(ListPrice) OVER() AS avrg,
ProductLine,
SellStartDate
FROM Production.Product

This worked. Thank you sir.

What about my other question about creating a new table, saving it and where to find it? Do you know where I can find the table I created? I can't find it anywhere in the 'tables' folder.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-26 : 17:40:46

It depends on what your current database was when you created the table (and also the server, and your default schema if you did not specify a schema). Run this query against your database, and if you don't get a row showing the tablename, run the query against any other databases you may have. Lot of times, logins are created with their default database being the master database. So unless you changed the database before you created the table, it may be in the master database that you created the table.

select * from sys.tables where name like '%yourtablename%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources