Transact-SQL - Help me - Count function

Please help me to sort out my problem!!!

I have 2 tables:

1. News (NewsID, UserID, Content)
2. User (UserID, Name)

I want to return a table such as:

| UserID | Name | Number of news posted by this user |
------------------------------------------------------
| | | |

I believe I should use a T-SQL such as:

CREATE PROCEDURE [dbo].[User_Get_All]

AS

SELECT
User.UserID,
User.Name,
COUNT ?????? "Number of news posted by this user"
FROM
User INNER JOIN News ON News.UserID = User.UserID

Thanks in advance

Hi, and welcome to the forums. I'm not entirely sure what you are trying to achieve, do you just want the name and userid of the usedr along with a count of the number of of news records posted by this user, or do you want to return the user's name and id along with all of the ews items on the same line? If you wanted to do the latter it would be more difficult, but to simply return a count of the news items you should be able to do something like this:

CREATE PROCEDURE [dbo].[User_Get_All]

AS
set nocount on
begin
SELECT
u.UserID,
u.Name,
(select COUNT(NewsID) from news where userid = u.userid)as cnt
FROM
User u

--or
-- when ever you use aggregate function and values then u ---should use group by clause or partion by clause (2005)
--SELECT u.UserID, u.Name,COUNT(n.NewsID)
--FROM
--User u INNER JOIN news n ON n.UserID = u.UserID
--GROUP BY u.UserID, u.Name

end
set nocount off

Hi,

I hope the below SQL query will be very Useful for you



Thanks & Regards
Sakthimeenakshi.S