SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Msg 8120 and creating new table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 03/25/2013 :  16:49:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 03/25/2013 :  16:53:36  Show Profile  Reply with Quote
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
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 03/25/2013 :  17:01:53  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 03/25/2013 :  17:20:11  Show Profile  Reply with Quote
See if this is what you are looking for:
SELECT 
   ListPrice,
   AVG(ListPrice) OVER() AS avrg,
   ProductLine,
   SellStartDate
FROM Production.Product

Edited by - James K on 03/25/2013 17:20:22
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/25/2013 :  17:24:13  Show Profile  Reply with Quote
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
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 03/26/2013 :  16:36:50  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 03/26/2013 :  17:40:46  Show Profile  Reply with Quote
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%'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000