0

Hi

I am trying to create a stored procedure to establish which user has upload the most videos on my site.

The tables are:

Video
VideoNo, VideoName, UserNo, VideoUrl

Users
UserNo, FirstName, LastName

Each entry on the video table uses the users number. I would like to return a query that gives me the userNo, FirstName, LastName and a new column with Total uploads of all their videos.

Is this possible?

Thanks

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by BitBlt
0

Yes, it is possible. If we assume you are using MS SQL Server 2008, then this query should give you what you need. Just use it as the basis for creating your stored proc and you should be fine.
select top 1
a.userno, a.FirstName, a.LastName, COUNT(b.videono) as TotalUploads
from dbo.Users a
left join dbo.Video b
on a.UserNo = b.UserNo
group by a.userno, a.FirstName, a.LastName
order by COUNT(b.videono) desc
There may be issues with what you return though...what if two users have the same number of uploads? This query will return only one row, and there's no way to predict which of the tied users will be returned. If you're okay with that, great. If not, then you need to adjust your thinking about how you design your app.

Hope this helps! Good luck!

Edited by BitBlt: Formatting

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.