Stored procedure question - 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 2008 Forums

Transact-SQL (2008)

Stored procedure question

Author

Topic

Michael27
Starting Member

5 Posts

Posted - 2010-06-12 : 12:26:11

[code]CREATE procedure [dbo].[Proc_name]
(@ID_client int)
as

SELECT Movies.Name, Movies.Price, Rented.Date, Users.User_ID
FROM Movies INNER JOIN
Rented ON Movies.Movie_ID = Rented.Movie_ID INNER JOIN
Users ON Rented.User_ID = Users.User_ID
WHERE (Users.User_ID = @ID_client ) [/code]

How to modify this SQL query so that it displays SUM(Movies.Price) not just individual price

Example
Price Column has values 10,20,30 i want to display SUM() instead

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-12 : 15:12:54

just use:
sum(Movies.Price) over (partition by Users.User_ID) as Price

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael27
Starting Member

5 Posts

Posted - 2010-06-12 : 19:12:11

I'm afraid i don't understand completely
Could you write the query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-13 : 02:49:02

[code]
CREATE procedure [dbo].[Proc_name]
(@ID_client int)
as

SELECT Movies.Name,
SUM(Movies.Price) OVER (PARTITION BY Users.User_ID) as Price,
Rented.Date,
Users.User_ID
FROM Movies INNER JOIN
Rented ON Movies.Movie_ID = Rented.Movie_ID INNER JOIN
Users ON Rented.User_ID = Users.User_ID
WHERE (Users.User_ID = @ID_client )
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael27
Starting Member

5 Posts

Posted - 2010-06-13 : 03:17:12

It worked.
Thanks webfred.

Next question is how does it work
This part

   SUM(Movies.Price) OVER (PARTITION BY Users.User_ID) as Price,

Kristen
Test

22859 Posts

Posted - 2010-06-13 : 06:14:31

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Michael27
Starting Member

5 Posts

Posted - 2010-06-13 : 18:45:05

Sorry to bother again,
Same query .
If the Rented.Date(IS DATETIME) is more then 24 Hours then then price goes up TIMES(*) 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources