View Problem(HELP!) - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

View Problem(HELP!)

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-02 : 17:15:46

CREATE VIEW Sales AS Select Authors.First_Name, Authors.Last_Name, SUM(Works.Sales)*Works.Price
AS 'Total_Sales_$' From Works INNER JOIN Authors ON (Works.Author_ID =
Authors.Author_ID ) Group By Authors.First_Name,Authors.Last_Name,Works.Price

Select * from Sales
Results:

First_Name Last_Name Total_Sales_$
Anne Cohen 10194000.00
Glen Harris 279860.00
Lenard Dawson 95880.00
Lenard Dawson 21978.00
Lynn Steele 6796000.00
Robert Balzar 2719500.00
Robert Balzar 269910.00

How do i combine the sales with the same name?

Thank you in Advance
Danny D.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-02 : 17:19:07

Remove the Works.Price in your GROUP BY:

CREATE VIEW Sales AS Select Authors.First_Name, Authors.Last_Name, SUM(Works.Sales)*Works.Price
AS 'Total_Sales_$' From Works INNER JOIN Authors ON (Works.Author_ID =
Authors.Author_ID ) Group By Authors.First_Name,Authors.Last_Name

SQL Server Helper
http://www.sql-server-helper.com

Danny4003
Starting Member

40 Posts

Posted - 2007-01-02 : 17:24:30

I tried it and Gives me an Error:

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

Thanks,
Danny

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 17:41:24

That is because you assume all sales have same price.
Use this for correct logic...

CREATE VIEW vwSales
AS

SELECT		Authors.First_Name,
		Authors.Last_Name,
		SUM(Works.Sales * Works.Price) AS 'Total_Sales_$'
FROM		Works
INNER JOIN	Authors ON Authors.Author_ID = Works.Author_ID
GROUP BY	Authors.First_Name,
		Authors.Last_Name

Peter Larsson
Helsingborg, Sweden

Danny4003
Starting Member

40 Posts

Posted - 2007-01-02 : 18:09:20

Thank you Peso

Danny D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources