Using a table variable twice in a query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using a table variable twice in a query

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-20 : 07:47:15

Hi all

The following code declares a table variable and fills it with data, showing some names, department ID's, and gender ID's.
I then select the totals by gender for each department (I know this could be improved to show 0 totals but please bear with me!):


declare @tmp table	
	(
	ID int IDENTITY(1,1),
	[name] nvarchar(50),
	gender int,
	department int
	)

Insert Into @tmp ([name], gender, department) VALUES ('Bob', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Mick', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Roger', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Jane', 2, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Neil', 1, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Debbie', 2, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Sue', 2, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Frank', 1, 3)
Insert Into @tmp ([name], gender, department) VALUES ('Mo', 1, 3)
Insert Into @tmp ([name], gender, department) VALUES ('Tee', 1, 3)

select * from @tmp

select
	tmp1.department,
	tmp1.gender,
	count (*) as totals
from
	@tmp tmp1
group by
	department, gender
order by 
	department

Can anyone explain how I can improve the query to show the total number of people in each department in the same set of results.
e.g. I need to also include this bit of code:


select
	tmp1.department,
	count (*) as totals
from
	@tmp tmp1
group by
	department
order by 
	department

So basically what I need to show is:

how many of each gender are in each department

how many people total are there in each department (as a direct comparison)

The aim is for me to see how the same table variable is referenced twice in the same statement...
I hope that makes sense...!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-20 : 07:56:49

Something like this?


declare @tmp table	
	(
	ID int IDENTITY(1,1),
	[name] nvarchar(50),
	gender int,
	department int
	)

Insert Into @tmp ([name], gender, department) VALUES ('Bob', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Mick', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Roger', 1, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Jane', 2, 1)
Insert Into @tmp ([name], gender, department) VALUES ('Neil', 1, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Debbie', 2, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Sue', 2, 2)
Insert Into @tmp ([name], gender, department) VALUES ('Frank', 1, 3)
Insert Into @tmp ([name], gender, department) VALUES ('Mo', 1, 3)
Insert Into @tmp ([name], gender, department) VALUES ('Tee', 1, 3)


select
	tmp1.department,
	tmp1.gender,
	count(*) as gender_totals,
	(select count(*) from @tmp  tmp2 where tmp2.department = tmp1.department) as totals
from
	@tmp tmp1 
group by
	tmp1.department, tmp1.gender
order by 
	tmp1.department

Harsh Athalye
http://www.letsgeek.net/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-20 : 08:09:11

Hi

Thanks for your response - yes that is what I was looking to achieve.

Could you please just explain how your statement works with the nested select? I was kind of expecting to have to join the table to itself for some reason. Is that approach an alternative or not correct at all?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-20 : 08:18:35

What I have used here is a correlated subquery. Join would have worked but since you want totals to be displayed by gender as well as grand total, this is the simplest way to achieve it.

What my query does is to count the total number of records for each department in outer query and output it as a separate column.

Harsh Athalye
http://www.letsgeek.net/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-20 : 09:24:33

Okay great. Thanks very much once again :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources