December 13, 2011 at 9:40 am
I need to find intersecting records from a table in the form of count.,
How many User Id count where app Id is 4
How many User Id count where app Id is 8
How many User Id count where app Id is 128
How many User Id count where app Id is both 4 and 8 (intersection only)
How many User Id count where app Id is both 4 and 128 (intersection only)
How many User Id count where app Idis both 8 and 128 (intersection only)
How many User Id count where app Id is all 4 and 8 and 128 (intersection only)
Example table data
USER_IDApplication_IDMDMOrganizationName
1502374DISCOVERY.COM
1502394The Wall Street Journal
1502574DISCOVERY.COM
1502578Encyclopedia Britannica Inc.
1503168Bloomberg BusinessWeek
1503734DISCOVERY.COM
1503984DISCOVERY.COM
150316128Enslow Publishers Inc.
150414128CITIZEN MAGAZINE
1507304AP IMAGES ONLINE SALES (KO)
December 13, 2011 at 9:48 am
Join the table to itself on whatever column makes them match, User_ID in this case, and on the intersecting program IDs. That'll give you a count you can use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 10:05 am
I would personally just use a group by clause on the application_id
December 13, 2011 at 10:19 am
Yes I did the Group by Clause along with Having clause for conditions where I need to find APP Id in (4 , 8) or (8,128)..so on..
select COUNT(*) cnt, User_ID from #temp where Application_ID in (8,128) and application_id <> 4
group by User_ID
having COUNT(*) > 1
December 13, 2011 at 10:49 am
I re-read my answer and the question and realized I didn't answer the question. Gsquared has it right, self joining the table to itself will give you the answer.
code isn't tested but should give you an idea of what to do, without completely giving the answer away
select
count(*)
from
temp t1
inner join temp t2 ON t1.Key= t2.Key
where
t1.Field1 = 1
AND t2.Field1 = 2
December 13, 2011 at 10:51 am
If the data set is not very large, a brute-force method works well enough:
DECLARE @Example TABLE
(
[user_id] INTEGER NOT NULL,
application_id INTEGER NOT NULL,
organization_name VARCHAR(50) NOT NULL,
PRIMARY KEY (application_id, [user_id])
)
INSERT @Example
([user_id], application_id, organization_name)
VALUES
(150237, 4, 'DISCOVERY.COM'),
(150239, 4, 'The Wall Street Journal'),
(150257, 4, 'DISCOVERY.COM'),
(150257, 8, 'Encyclopedia Britannica Inc.'),
(150316, 8, 'Bloomberg BusinessWeek'),
(150373, 4, 'DISCOVERY.COM'),
(150398, 4, 'DISCOVERY.COM'),
(150316, 128, 'Enslow Publishers Inc.'),
(150414, 128, 'CITIZEN MAGAZINE'),
(150730, 4, 'AP IMAGES ONLINE SALES (KO)')
-- Intersection of 4 & 8
SELECT COUNT_BIG(*) FROM (
SELECT e.[user_id] FROM @Example AS e
WHERE e.application_id = 4
INTERSECT
SELECT e.[user_id] FROM @Example AS e
WHERE e.application_id = 8
) AS u
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2011 at 10:53 am
Not very large and count_big are somewhat mutually exclusive in my mind :-D.
December 13, 2011 at 10:54 am
Ninja's_RGR'us (12/13/2011)
Not very large and count_big are somewhat mutually exclusive in my mind :-D.
I use it because COUNT introduces a pointless conversion to integer. Saves me a Compute Scalar in the query plan. Habit.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2011 at 10:56 am
SQL Kiwi (12/13/2011)
Ninja's_RGR'us (12/13/2011)
Not very large and count_big are somewhat mutually exclusive in my mind :-D.I use it because COUNT introduces a pointless conversion to integer. Saves me a Compute Scalar in the query plan. Habit.
How many minutes of processing does that save you on your prod servers? 😉
December 13, 2011 at 11:07 am
Ninja's_RGR'us (12/13/2011)
How many minutes of processing does that save you on your prod servers? 😉
None, but I have a tidy mind. Can we get back to the question now?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2011 at 11:10 am
SQL Kiwi (12/13/2011)
Ninja's_RGR'us (12/13/2011)
How many minutes of processing does that save you on your prod servers? 😉None, but I have a tidy mind. Can we get back to the question now?
t'was a real question. Your servers are much busier than mine.
As for the OP's question I think we are already done.
December 13, 2011 at 11:13 am
Guys,
Both of these worked for me. 🙂
Thanks a lot.
1) select COUNT(*) cnt from (
select COUNT(*) cnt , USER_ID from #temp where Application_ID in (4,8) and application_id <> 128
group by USER_ID having COUNT(*) = 2 ) t
and
2) select COUNT(*) from (select User_id from #temp where Application_ID = 4
intersect select User_id from #temp where Application_ID = 8) as u
) as u
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply