3 Replies - 749 Views - Last Post: 05 August 2012 - 03:16 AM Rate Topic: -----

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,011
  • Joined: 12-January 10

Get Count of One Table in Multitable Query

Posted 31 July 2012 - 01:39 PM

how can i get the count of one table in a multi table query.

SELECT     *, ((count(*)from t_User_Trace group by t_user_trace.txt_IP_Address) as trace_count )
FROM         t_Employee 
INNER JOIN t_Vendor_Employee WITH (NOLOCK) ON t_Employee.employee_ID = t_Vendor_Employee.employee_ID 
INNER JOIN t_User WITH (NOLOCK) ON t_Employee.user_id = t_User.user_ID 
INNER JOIN t_User_Group ON t_User.user_group_ID = t_User_Group.user_group_ID
inner join t_User_Trace on t_User.txt_username = t_User_Trace.txt_username
WHERE     (t_Vendor_Employee.vendor_employee_id = 642391) 
--(select  COUNT(*)as _count  from t_User_Trace


i need to count the records in t_User_Trace and I cnat remeber how to do it. But at the same time I also need to return everything from all the above tables.

This post has been edited by macosxnerd101: 31 July 2012 - 01:40 PM
Reason for edit:: Please use a descriptive titel


Is This A Good Question/Topic? 0
  • +

Replies To: Get Count of One Table in Multitable Query

#2 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10397
  • View blog
  • Posts: 38,468
  • Joined: 27-December 08

Re: Get Count of One Table in Multitable Query

Posted 31 July 2012 - 01:41 PM

DarenR, you have more than 850 posts. Please use a descriptive title. "I have a question" and "I forgot how to" are not descriptive titles.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,011
  • Joined: 12-January 10

Re: Get Count of One Table in Multitable Query

Posted 31 July 2012 - 01:42 PM

I was just about to change that lol but you beat me to it-- thanks
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Get Count of One Table in Multitable Query

Posted 05 August 2012 - 03:16 AM

You're nearly there with the query, but you need to do your aggregating within a subquery, so that you can GROUP within that subquery, thus avoiding SELECT DISTINCT or GROUP BY in your outer query...
SELECT
	*,
	UserTraceAggregate.trace_count
FROM t_Employee 
INNER JOIN t_Vendor_Employee WITH (NOLOCK) ON t_Employee.employee_ID = t_Vendor_Employee.employee_ID 
INNER JOIN t_User WITH (NOLOCK) ON t_Employee.user_id = t_User.user_ID 
INNER JOIN t_User_Group ON t_User.user_group_ID = t_User_Group.user_group_ID
INNER JOIN (
	SELECT
		COUNT(*) AS trace_count,
		txt_IP_Address,
		txt_username
	FROM t_User_Trace
	GROUP BY
		txt_IP_Address,
		txt_username
) AS UserTraceAggregate ON t_User.txt_username = t_User_Trace.txt_username
WHERE (t_Vendor_Employee.vendor_employee_id = 642391)


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1