Help w/subquery to get total unique

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 2000 Forums

Transact-SQL (2000)

Help w/subquery to get total unique

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-13 : 17:57:28

Hello all...

Okay, I am getting closer...to the need for adding a total of unique users to my existing query. But I still need help in adding the correlated subquery to my existing query. (see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64641)

I need to get the count of all unique users for each location...and there can be only one row per userid in TableA...but there can be multiple rows for each userid in the history table - Audit_TableA.

To get the unique userid rows I stumbled across correlated subqueries

This works stand alone:
select count(userId) AS Temp
FROM ( SELECT userID from TableA where location = 'Building A' union select userID from audit_TableA where location = 'Building A') BothTable

The results are the unique userIDs from both tables...not duplicates. Which is what I need.

But, when I try to add this to my bigger query below...it does not work...more specifically, I get some "Incorrect Syntax" errors that I cannot fix.

SELECT Location,
sum(case when LocationStatus = 'Arrived'
then 1
else 0
end) as Arrived,
sum(case when LocationStatus = 'Departed'
then 1
else 0
end) as Departed,
sum(case WHEN (LocationStatus = 'Waiting'
then 1
else 0
end ) AS Waiting,
-- I tried adding the subquery here....but to no avail....
FROM (SELECT Location, LocationStatus, UserID
FROM TableA
UNION ALL
(SELECT Location, LocationStatus, UserID
FROM Audit_TableA
UNION SELECT Location, LocationStatus, UserID
FROM Audit_TableA) ) TableAlias
GROUP BY Location

Any help will be appreciated.

Thanks
- will

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-14 : 22:07:34

Hello all...

I spent most of the day trying to get this to work, but I still cannot get the unique count of user ids from the current table (TableA) and the historical/audit table (Audit_TableA).

If anyone knows of any way to do this, I would appreciate the help.

thanks
- will

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 22:39:28

sum(case WHEN (LocationStatus = 'Waiting'
then 1
else 0
end ) AS Waiting,
users = (select count(userId) AS Temp
FROM ( SELECT userID from TableA where location = 'Building A' union select userID from audit_TableA where location = 'Building A') BothTable)
FROM (SELECT Location, LocationStatus, UserID
FROM TableA
UNION ALL
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 02:37:14

This is getting me much closer to the result I need. All I have to do now is to get that total user count to be for each location in my result set rather than for all locations.

Thank you very much.

- will

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 04:40:55

Actually, it appears that I cannot get the unique total for every location within a single query. I can get the total of unique users for all locations, but not for each location.

Looks like I will have to resort to a Cursor or something similar.

Thanks again....wish this would have worked.
- willl

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 09:10:02

I think you mean that you can't work out how to do it.
Sounds like you've given up on this.

Reply if you're still interested in a solution.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 09:19:24

I'll post a suggestion anyway.
It would be better as a derived tabe in the where clause but something like this should work.

sum(case WHEN (LocationStatus = 'Waiting'
then 1
else 0
end ) AS Waiting,
users = (select count(distinct userId) FROM ( SELECT a2.userID, a2.location from TableA a2 union all select b2.userID, b2.location from audit_TableA b2) a3 where a3.location = TableA.location)
FROM (SELECT Location, LocationStatus, UserID
FROM TableA
UNION ALL
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 15:37:01

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 15:39:57

What do you get that's incorrect when you run my last suggestion?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 15:45:38

The count for unique users is not correct.

In one of my sample sets...I have a total of 16 unique users betwen TableA and Audit_TableA.

If I run queries to get the distinct userids from each location (and do a UNION to remove the duplicate user ids) I get different values than what I get when I use your t-sql derived table code.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 15:50:48

That's odd - how different are they?
Which gives the greater value?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 15:59:17

In instances the results are VERY different.

Here are three examples:

Real Data:
Location A = 1
Location B = 3
Location C = 4

Using the derived table stuff in my query, I get:
Location A = 6
Location B = 10
Location C = 14

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 16:03:44

I should add...as I just queried the two tables...there are locations that might appear in TableA but not in the Audit_TableA...and vice-versa.

Not sure if that has anything to do with the counts be off....

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 17:06:46

I'll try it on the sample data you gave.
You did put the count(distinct userid) in didn't you.
i.e. included the distinct.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 17:11:16

Yes...I did use the "distinct".

And...I left out one thing in regards to the sample data...there should be one Location in the Audit_TableA data (Building D) that is not in TableA. There can be locations that users were at in the past (in the Audit_TableA)...but no one might have that as their current location....hence no rows in TableA with that location.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 17:25:21

Here's the query with a derived table in the from clause rather than the select. It gives the expected results from your data.
I changed your query to just have a single union as I think that's what you want.

create table #TableA (Location varchar(20), LocationStatus varchar(20), UserID int)
create table #Audit_TableA (Location varchar(20), LocationStatus varchar(20), UserID int)
insert #TableA select 'Building A', 'Arrived', 1234
insert #TableA select 'Building A', 'Arrived', 4567
insert #TableA select 'Building B', 'Arrived', 6543
insert #TableA select 'Building A', 'Departed', 9887
insert #TableA select 'Building C', 'Arrived', 1122
insert #TableA select 'Building B', 'Departed', 5544

insert #Audit_TableA select 'Building A', 'Arrived', 1234
insert #Audit_TableA select 'Building A', 'Departed', 9988
insert #Audit_TableA select 'Building C', 'Arrived', 2234
insert #Audit_TableA select 'Building B', 'Waiting', 1234
insert #Audit_TableA select 'Building B', 'Arrived', 1234
insert #Audit_TableA select 'Building C', 'Waiting', 2234

select Location ,
users = (select count(distinct userId)
FROM ( SELECT a2.userID, a2.location from #TableA a2 union all select b2.userID, b2.location from #audit_TableA b2) a3 where a3.location = TableAlias.location)
select TableAlias.Location ,
users = max(a3.Users)
FROM
(SELECT Location, LocationStatus, UserID FROM #TableA
UNION
SELECT Location, LocationStatus, UserID FROM #Audit_TableA
) TableAlias
join
(select Users = count(distinct UserID), Location from (SELECT a2.userID, a2.location from #TableA a2 union all select b2.userID, b2.location from #audit_TableA b2) a group by Location
) a3
on a3.location = TableAlias.location
GROUP BY TableAlias.Location

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 17:46:07

I just ran what you sent (copy and pasted everything) and I get an "Invalid column name 'Location'" message.

Let me make sure that I didn't drop something in the copy/paste operations......

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 17:56:37

Sorry - I left an extra query in the middle

create table #TableA (Location varchar(20), LocationStatus varchar(20), UserID int)
create table #Audit_TableA (Location varchar(20), LocationStatus varchar(20), UserID int)
insert #TableA select 'Building A', 'Arrived', 1234
insert #TableA select 'Building A', 'Arrived', 4567
insert #TableA select 'Building B', 'Arrived', 6543
insert #TableA select 'Building A', 'Departed', 9887
insert #TableA select 'Building C', 'Arrived', 1122
insert #TableA select 'Building B', 'Departed', 5544

insert #Audit_TableA select 'Building A', 'Arrived', 1234
insert #Audit_TableA select 'Building A', 'Departed', 9988
insert #Audit_TableA select 'Building C', 'Arrived', 2234
insert #Audit_TableA select 'Building B', 'Waiting', 1234
insert #Audit_TableA select 'Building B', 'Arrived', 1234
insert #Audit_TableA select 'Building C', 'Waiting', 2234

select TableAlias.Location ,
users = max(a3.Users)
FROM
(SELECT Location, LocationStatus, UserID FROM #TableA
UNION
SELECT Location, LocationStatus, UserID FROM #Audit_TableA
) TableAlias
join
(select Users = count(distinct UserID), Location from (SELECT a2.userID, a2.location from #TableA a2 union all select b2.userID, b2.location from #audit_TableA b2) a group by Location
) a3
on a3.location = TableAlias.location
GROUP BY TableAlias.Location

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-15 : 20:00:56

That did it. I was able to modify it to fit my actual needs - some additional column requirements and tables. But this solution works fantastic.

I don't know how you even came up with this. Is it some sort of magic or alchemy...the ability to turn my badly formed sql statements into a bit of art and science? Do you have some direct link to the Aliens or their technology that is being carefully researched at Area 51? I am not sure...but I offer my thanks and more thanks. Thank you.

- will

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 20:08:12

Thanks.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-15 : 20:10:59

Isn't this what you are trying to accomplish?

select	...
	,count(distinct userID)
from	(
		select	Location, LocationStatus, UserID from TableA
		union
		select	Location, LocationStatus, UserID from Audit_TableA
	) as tableAlias
group by
	Location

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources