Member Avatar for ndraycott

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
  • 100 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for BitBlt

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 because: Formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.