help combining queries - SQL Server Forums

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 combining queries

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-16 : 20:27:25

Hi,

I have a query in which I want to change the "ORDER BY" on. The new way I want to calculate it is going to require much, much more resources. I'm not even sure its going to be possible to execute this in a decent time frame.

Basically in query #1 I have 2 columns that are basically static (votes/points) and I simply divide them.

My new method is more advanced. What I want to do is still do the same SELECT as done in query #1, but I want to ORDER BY the 'rootMeanSquare' value that is calculated in query #2.

The voteForID column in both voting tables should correspond with tblUserDetails.userID.

Thanks very much for any assistance! :)
mike123

query#1

SELECT TOP 5000 nameOnline, userID
FROM tblUserDetails WHERE active = 1 and votes > 30 ORDER BY (points / (votes * 1.0)) DESC

query#2

CREATE PROCEDURE [dbo].[select_VoteHistory]
(
@userID int
)
AS SET NOCOUNT ON

Select

t.votes_10,
t.votes_15,
t.votes_20,
t.totalVotes,

(SQRT
(((t.votes_10 * 10.0 * 10.0) +
(t.votes_15 * 15.0 * 15.0) +
(t.votes_20 * 20.0 * 20.0) )

/ TotalVotes)) / 10 as 'rootMeanSquare'

FROM

(
SELECT SUM(votes_10) as votes_10, SUM(votes_15) as votes_15, SUM(votes_20) as votes_20, SUM(TotalVotes) as totalVotes

FROM

(

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',

COUNT(*) AS TotalVotes FROM tblRandomVote

WHERE voteForID = @userID

UNION

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',
COUNT(*) AS TotalVotes FROM tblUserVote

WHERE voteForID = @userID

) a

) t

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-20 : 19:34:37

bump if anyone can offer any assistance on this one ! .. still havent been able to figure it out on my own =[

much apprecated

mike123

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-21 : 15:02:29

Can you change the UNION to UNION ALL?

As for the ORDER you can simply add:

ORDER BY
	(SQRT(((t.votes_10 * 10.0 * 10.0) + 
	(t.votes_15 * 15.0 * 15.0) + 
	(t.votes_20 * 20.0 * 20.0) )
	/ TotalVotes)) / 10

Also, I think you can lose the ISNULL check as the SUM/CASE should get you a 0 if points is NULL.

Are you having a particular performance issue that you need help with also?

-Ryan

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-21 : 17:16:21

Hi Ryan,

Thanks for the input. The part I really don't understand is how I am supposed to JOIN these two queries together? I think when I add this JOIN I might experience performance problems.

Thanks again,
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-21 : 17:44:16

Mike,

Where is the DDL for your tables and DML for sample data? Surely if you'd posted this information in your original post on May 16th, you'd have an answer by now.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-21 : 19:12:32

Hey Tara,

Sorry about that. Here is the DDL and DML I think you are looking for. Please let me know if theres anything else I can add.

Thanks again! :)




CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[NameOnline] [varchar](15) NULL,
	[GenderID] [tinyint] NULL,
	[Votes] [int] NULL  DEFAULT (0),
	[Points] [int] NULL  DEFAULT (0),
	[Active] [tinyint] NULL
	)
 



CREATE TABLE [dbo].[tblUserVote](
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voteForID] [int] NOT NULL,
	[voterID] [int] NOT NULL,
	[date] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
	)



CREATE TABLE [dbo].[tblRandomVote](
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voteForID] [int] NOT NULL,
	[IP] [varchar](15) NOT NULL,
	[date] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
) ON [PRIMARY]



--the votes,points in this table will be phased out so I have 0 as the value in there
INSERT INTO tblUserDetails (nameonline,genderID,votes,points,active) VALUES ('bobby','1','0','0','1')
INSERT INTO tblUserDetails (nameonline,genderID,votes,points,active) VALUES ('billy','1','0','0','1')
INSERT INTO tblUserDetails (nameonline,genderID,votes,points,active) VALUES ('george','1','0','0','1')
INSERT INTO tblUserDetails (nameonline,genderID,votes,points,active) VALUES ('leroy','1','0','0','1')


INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('1','2',getDate(),'85')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('1','3',getDate(),'90')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('1','4',getDate(),'100')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('2','1',getDate(),'10')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('2','3',getDate(),'25')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('2','4',getDate(),'45')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('3','1',getDate(),'75')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('3','2',getDate(),'80')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('3','4',getDate(),'90')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('4','1',getDate(),'15')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('4','2',getDate(),'20')
INSERT INTO tblUserVote (voteForID,voterID,date,points) VALUES ('4','3',getDate(),'65')



INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('1','127.0.0.1',getDate(),'70')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('2','127.0.0.1',getDate(),'75')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('3','127.0.0.1',getDate(),'80')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('4','127.0.0.1',getDate(),'85')

INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('1','192.168.1.1',getDate(),'70')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('2','192.168.1.1',getDate(),'75')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('3','192.168.1.1',getDate(),'80')
INSERT INTO tblRandomVote (voteForID,IP,date,points) VALUES ('4','192.168.1.1',getDate(),'85')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-21 : 19:15:44

The last thing we need is the expected result set that should be returned when the query runs on that sample data.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-21 : 19:48:11

Hi Tara,

the results would be brought back in the following order

userID,rating
1, 8.381
3, 8.114
4, 6.212
2, 5.291

I worked this out by hand be doing the steps below

Thanks again!! :)



userID 1 points= 85,90,100,70,70 (square each value) -> (7225+8100+10000+4900+4900)  average = 35125/5 = 7025  ->  square root = 83.81527 / 10 =  FINAL ANSWER = 8.381
userID 2 points= 10,25,45,75,75 (square each value) -> (100+625+2025+5625+5625)      average = 14000/5 = 2800  ->  square root = 52.91502 / 10 =  FINAL ANSWER = 5.291
userID 3 points= 75,80,90,80,80 (square each value) -> (5625,6400,8100,6400,6400)    average = 32925/5 = 6585  ->  square root = 81.14801 / 10 =  FINAL ANSWER = 8.114
userID 4 points= 15,20,65,85,85 (square each value) -> (225,400,4225,7225,7225)      average = 19300/5 = 3860  ->  square root = 62.12889 / 10 =  FINAL ANSWER = 6.212

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-22 : 12:45:25

Hopefully someone will come along to help you with this shortly. I'm in a cast right now, so it's very hard for me to type (which is why I haven't been on the board much).

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-22 : 12:59:17

This query works for the sample data and expected results.

DECLARE @UserDetail TABLE 
(
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[NameOnline] [varchar](15) NULL,
	[GenderID] [tinyint] NULL,
	[Votes] [int] NULL  DEFAULT (0),
	[Points] [int] NULL  DEFAULT (0),
	[Active] [tinyint] NULL
	)
 
DECLARE @UserVote TABLE (
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voteForID] [int] NOT NULL,
	[voterID] [int] NOT NULL,
	[date] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
	)


DECLARE @RandomVote TABLE(
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voteForID] [int] NOT NULL,
	[IP] [varchar](15) NOT NULL,
	[date] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
) 



--the votes,points in this table will be phased out so I have 0 as the value in there
INSERT INTO @UserDetail (nameonline,genderID,votes,points,active) VALUES ('bobby','1','0','0','1')
INSERT INTO @UserDetail (nameonline,genderID,votes,points,active) VALUES ('billy','1','0','0','1')
INSERT INTO @UserDetail (nameonline,genderID,votes,points,active) VALUES ('george','1','0','0','1')
INSERT INTO @UserDetail (nameonline,genderID,votes,points,active) VALUES ('leroy','1','0','0','1');


INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('1','2',getDate(),'85')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('1','3',getDate(),'90')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('1','4',getDate(),'100')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('2','1',getDate(),'10')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('2','3',getDate(),'25')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('2','4',getDate(),'45')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('3','1',getDate(),'75')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('3','2',getDate(),'80')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('3','4',getDate(),'90')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('4','1',getDate(),'15')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('4','2',getDate(),'20')
INSERT INTO @UserVote (voteForID,voterID,date,points) VALUES ('4','3',getDate(),'65');



INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('1','127.0.0.1',getDate(),'70')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('2','127.0.0.1',getDate(),'75')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('3','127.0.0.1',getDate(),'80')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('4','127.0.0.1',getDate(),'85')

INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('1','192.168.1.1',getDate(),'70')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('2','192.168.1.1',getDate(),'75')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('3','192.168.1.1',getDate(),'80')
INSERT INTO @RandomVote (voteForID,IP,date,points) VALUES ('4','192.168.1.1',getDate(),'85')


SELECT
	nameOnline, 
	userID,
	t.TotalPoints,
	t.TotalVotes,
	SQRT ((t.TotalPoints * 1.0) / t.TotalVotes) / 10 as 'rootMeanSquare' 

FROM 
	(
		SELECT 
			voteForID,
			SUM(TotalPoints) as TotalPoints, 
			SUM(TotalVotes) as TotalVotes 
		FROM
		(

			SELECT
				voteForID,
				SUM(POWER(CAST(points AS INT), 2)) as TotalPoints,
				COUNT(*) AS TotalVotes 
			FROM 
				@RandomVote 
			GROUP BY
				voteForID
		
			UNION ALL
		
			SELECT
				voteForID,
				SUM(POWER(CAST(points AS INT), 2)) as TotalPoints,
				COUNT(*) AS TotalVotes 
			FROM 
				@UserVote 
			GROUP BY
				voteForID
		) a 
		GROUP BY
			voteForID

	) t
INNER JOIN
	@UserDetail ud
	ON t.voteForID = ud.UserID
GROUP BY
	nameOnline, 
	userID,
	t.TotalPoints,
	t.TotalVotes
ORDER BY
	SQRT((t.TotalPoints * 1.0) / t.TotalVotes) / 10

-Ryan

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-23 : 00:17:26

Ouch Tara !

I wish you a speedy recovery !!

Lamprey,

This worked perfectly, however I discovered my "algorithm" produces pretty horrible results. I've had to step back to the drawing board and add some more factors into my calculation. This is what I have so far:

I would like to add the following 2 columsn to the query, but I'm unsure exactly the best way to do this. Also I'm not sure of the efficiency of the way I am doing things (calculationg the column "Average_of_Averages" twice. Also the way I could calculate "Average_of_Averages" from "rootMeanSquare" and "ratingAverage" rather than from scratch again.

Thanks VERY MUCH for any assistance !! :)
mike123


--ADD THESE TWO COLUMNS BELOW TO THE BOTTOM QUERY
(select count(userID) as tblthumb_count from tblExtraPhotos EP where status = 1 AND tblUserDetails.userid = EP.userid) as thumb_count, 
 

case when exists (select userID from tblExtraPhotos where tblUserDetails.userid = tblExtraPhotos.userid AND active = 1 AND photoID =99 ) then 
	'Y' 
else 
	'N' 
end as photoExists



SELECT TOP 50
	nameOnline, 
	userID,

 
	emailaddress,
	IP,

	t.TotalPoints,
	t.TotalVotes,
	SQRT ((t.TotalPoints * 1.0) / t.TotalVotes) / 10 as 'rootMeanSquare',
	(points * 1.0) / (votes * 1.0) / 10  as 'ratingAverage',
	
	((SQRT ((t.TotalPoints * 1.0) / t.TotalVotes) / 10) + (points * 1.0) / (votes * 1.0) / 10) / 2 as 'Average_of_Averages'

FROM 
	(
		SELECT 
			voteForID,
			SUM(TotalPoints) as TotalPoints, 
			SUM(TotalVotes) as TotalVotes 
		FROM
		(

			SELECT
				voteForID,
				SUM(POWER(CAST(uv.points AS INT), 2)) as TotalPoints,
				COUNT(*) AS TotalVotes 
			FROM 
		 
				tblUserVote UV
				  JOIN tblUserDetails UD2 on UD2.userID = UV.voterID

			WHERE UD2.LoginCount > 50


			GROUP BY
				voteForID
		
			UNION ALL
		
			SELECT
				voteForID,
				SUM(POWER(CAST(points AS INT), 2)) as TotalPoints,
				COUNT(*) AS TotalVotes 
			FROM 
				tblRandomVote 
			GROUP BY
				voteForID
		) a 
		GROUP BY
			voteForID

	) t
INNER JOIN
	tblUserDetails ud
	ON t.voteForID = ud.UserID



WHERE totalVotes > 30 and votes > 30 and UD.genderID = 2 AND active = 1 

--and IP <> '68.2.26.249' and DateDiff(dd, lastLoggedIn, GetDate())  < 15


GROUP BY
	nameOnline, 
	userID,

	emailaddress,
	IP,
	points,votes,
	t.TotalPoints,
	t.TotalVotes



ORDER BY


--	SQRT((t.TotalPoints * 1.0) / t.TotalVotes) / 10 DESC
-- BELOW IS TO SORT BY "'Average_of_Averages'", perhaps there is a better way than recomputing it here as we do above already
((SQRT ((t.TotalPoints * 1.0) / t.TotalVotes) / 10) + (points * 1.0) / (votes * 1.0) / 10) / 2 DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources