help with query (returning counts)

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)

help with query (returning counts)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-19 : 08:14:07

Hi,

I have the following two tables as shown below. I am trying to write a query that brings back results like this

--Results should have these 3 columns, just 1 row brought back
--City_Count,StateProv_Count,Country_Count

I have a "meet me" section on this social network and users can click "YES" or "NO" on each user they view. Each YES and NO vote is recorded in the MeetMe table.

We want to have a SP that returns the count of users that remain in their local areas (users where an entry in the meetme table does not exist) . We should also factor in the type of user they are looking for (lookingfor_genderID)

Not exactly sure how to go about this, so any help is greatly appreciated.

Thanks once again!
mike123

CREATE TABLE [dbo].[tblMeetMe_Matches](
[userID] [int] NOT NULL,
[matchUserID] [int] NOT NULL,
[matchDate] [datetime] NOT NULL,
[wantstoMeet] [tinyint] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[City] [varchar](25) NULL,
[StateProvID] [tinyint] NULL,
[CountryID] [tinyint] NULL,
[GenderID] [tinyint] NULL,
[SexualityID] [tinyint] NULL,
[Active] [tinyint] NULL,
[lookingToMeet_genderID] [tinyint] NULL
)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 08:36:39

This will give you the count, I'll leave it to you to integrate the other piece


SELECT count(*)
FROM
  tblUserDetails ud
WHERE NOT EXISTS
  (select 1
   from tblMeetMe_Matches mmm
   where ud.userid = mmm.userid)

Jim

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-19 : 08:45:35

Appreciate the input, but not really sure where to go from here? I tried this but its giving me a result of 0

SELECT count(*)
FROM
tblUserDetails ud WHERE userID = 500
AND NOT EXISTS
(select 1
from tblMeetMe_Matches mmm
where ud.userid = mmm.userid)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 08:59:10

That means that UserID 500 in tblUserDetails doesn't exist in tblMeetMe_Matches. Also, if you specify a userID, the most your count will ever be is 1, and you no longer need tblUserDetails.

Jim

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-19 : 09:10:55

try this


Declare @UserID int
set @UserID = 500

select a.*,
(
	select count(distinct aaa.userID)
	from 
		tblUserDetails aaa
	left join
		tblMeetMe_Matches bbb
	on		aaa.UserID = bbb.UserID
	and		bbb.matchUserID = a.UserID
	where
	bbb.UserID is null
	--and aaa.GenderID = 1
	--and aaa.SexualityID = 1 
        /*etc ..Enter any of your criteria here for your counts
          (i.e. city state's whatever*/
) as MyCount
from
tblUserDetails a
where a.UserID = @UserID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-19 : 09:17:09

quote:
Originally posted by jimf
Also, if you specify a userID, the most your count will ever be is 1, and you no longer need tblUserDetails.

I'm not following you on this part ? I have to specify a userID because each user has their own set of possible "matches"

thx!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-19 : 09:19:14

quote:

Originally posted by Vinnie881

try this


Declare @UserID int
set @UserID = 500

select a.*,
(
	select count(distinct aaa.userID)
	from 
		tblUserDetails aaa
	left join
		tblMeetMe_Matches bbb
	on		aaa.UserID = bbb.UserID
	and		bbb.matchUserID = a.UserID
	where
	bbb.UserID is null
	--and aaa.GenderID = 1
	--and aaa.SexualityID = 1 
) as MyCount
from
tblUserDetails a
where a.UserID = @UserID

Hey Vinny,

This looks promising and executes extremely fast!!...

Instead of just having "mycount" how can we configure it so that we have results that look like this :

City_Count,StateProv_Count,Country_Count

Any suggestions? Your help is very much appreciated!

Thanks again :),
Mike123

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-19 : 09:24:30

In the query I commented out two lines with "--" that's where you filter so if you wanted to get your city count the query would be

Declare @UserID int
set @UserID = 500


select a.*,
(
	select count(distinct aaa.userID)
	from 
		tblUserDetails aaa
	left join
		tblMeetMe_Matches bbb
	on		aaa.UserID = bbb.UserID
	and		bbb.matchUserID = a.UserID
	where
	bbb.UserID is null
	and aaa.City = a.City
        and aaa.StateID = a.StateID
	and aaa.SexualityID = a.Sexuality
--and anything else you were looking for
) as MyCityCount
,(
	select count(distinct aaa.userID)
	from 
		tblUserDetails aaa
	left join
		tblMeetMe_Matches bbb
	on		aaa.UserID = bbb.UserID
	and		bbb.matchUserID = a.UserID
	where
	bbb.UserID is null
	and aaa.StateID = a.StateID
	and aaa.SexualityID = a.SexualityID
--and anything else you were looking for
) as MyStateCount
--etc
from
tblUserDetails a
where a.UserID = @UserID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-19 : 09:28:19

add this line in as well


select count(distinct aaa.userID)
	from 
		tblUserDetails aaa
	left join
		tblMeetMe_Matches bbb
	on		aaa.UserID = bbb.UserID
	and		bbb.matchUserID = a.UserID
	where
	bbb.UserID is null
        and aaa.UserID <> a.UserID
	and aaa.StateID = a.StateID
	and aaa.SexualityID = a.SexualityID

this way it wont count the user looking it us as one of the people.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-20 : 02:34:12

Can I play too?

SELECT TOP 1	s.CountryID AS CountryID,
		SUM(CASE WHEN x.matchUserID IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY s.CountryID) AS CountryCount,
		s.StateProvID AS StateProvID,
		SUM(CASE WHEN x.matchUserID IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY s.CountryID, s.StateProvID) AS StateCount,
		City,
		SUM(CASE WHEN x.matchUserID IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY s.CountryID, s.StateProvID, s.City) AS CityCount
FROM		dbo.tblUserDetails AS s
LEFT JOIN	(
			SELECT	matchUserID
			FROM	dbo.tblMeetMe_Matches
			WHERE	UserID = @UserID
				AND WantsToMeet = 1

			UNION ALL

			SELECT	@UserID
		) AS x ON x.matchUserID = s.UserID
ORDER BY		MAX(CASE WHEN s.UserID = @UserID THEN 1 ELSE 0 END) OVER (PARTITION BY s.CountryID, s.StateProvID, s.City) DESC

Now, I am not sure which columns are used for what in the Meet table. Feel free to replace if necessary.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources