Getting Count From Another Table

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

General SQL Server Forums

New to SQL Server Programming

Getting Count From Another Table

Author

Topic

njguy
Starting Member

16 Posts

Posted - 2012-05-10 : 14:52:36

SELECT Users.Name, COUNT(Ratings.UserId) as RankedTotal
FROM Users Users
LEFT OUTER JOIN Ratings Ratings
ON Users.UserId = Ratings.UserId
WHERE Users.Inactive = 0
GROUP BY Users.Name
ORDER BY Users.Name

The SQL above works correctly but I wanted to add another column to give me a count from another table. The Query for the count be.

SELECT COUNT(*)
FROM Ideas
WHERE Refrence is not null

How do I incorporate these two together.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 15:03:41

Since the tables don't seem to be related in anyway, not sure what the data tells you. But, if you need to you can do it like this:

SELECT
	Users.Name,
	COUNT(Ratings.UserId) AS RankedTotal,
	c.IdeasCount
FROM
	Users Users
	LEFT OUTER JOIN Ratings Ratings
		ON  Users.UserId = Ratings.UserId
	CROSS JOIN (SELECT COUNT(*) AS IdeasCount FROM Ideas WHERE	Refrence IS NOT NULL) c
WHERE
	Users.Inactive = 0
GROUP BY
	Users.Name
ORDER BY
	Users.Name

njguy
Starting Member

16 Posts

Posted - 2012-05-10 : 16:26:43

Thanks....but I am getting the following error based on the code you sent

8120 - '[Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'c.IdeasCount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQL State is: 42000'

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-10 : 17:10:01

quote:
Originally posted by njguy

Thanks....but I am getting the following error based on the code you sent

8120 - '[Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'c.IdeasCount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQL State is: 42000'

Add column c.IdeasCount in group by clause.

SELECT
	Users.Name,
	COUNT(Ratings.UserId) AS RankedTotal,
	c.IdeasCount
FROM
	Users Users
	LEFT OUTER JOIN Ratings Ratings
		ON  Users.UserId = Ratings.UserId
	CROSS JOIN (SELECT COUNT(*) AS IdeasCount FROM Ideas WHERE	Refrence IS NOT NULL) c
WHERE
	Users.Inactive = 0
GROUP BY
	Users.Name,c.IdeasCount
ORDER BY
	Users.Name

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 19:32:59

Thanks Vijay!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources