ORDER by a SQL Statment - 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 2000 Forums

SQL Server Development (2000)

ORDER by a SQL Statment

Author

Topic

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-27 : 20:17:05

I have the following SQL Statment but I need to add calculate the rating in the SQL Statment and then order by it. Im pulling up the rating from the TRACKS table but I need to grab it from the TRACKS_REVIEWS table but each rating has three columns which are added up as below...

strSelectedRating = (CDbl(TR_TECHNICAL)+CDbl(TR_ADRENALINE)+CDbl(TR_ORIGINALITY))/3

It divides the rating by 3 to get the average of all three ratings for each review. How can I add this into this SQL Statment?

strSql2 = "SELECT TOP 10 T_ID"
strSql2 = strSql2 & ", T_MEMBERID"
strSql2 = strSql2 & ", M_NAME"
strSql2 = strSql2 & ", T_NAME"
strSql2 = strSql2 & ", T_FILE"
strSql2 = strSql2 & ", T_UPLOADDATE"
strSql2 = strSql2 & ", T_DOWNLOADED"
strSql2 = strSql2 & ", T_PICTHUMBPREVIEW"
strSql2 = strSql2 & ", T_SIMULATIONID"
strSql2 = strSql2 & ", TS_NAME"
strSql2 = strSql2 & ", TS_PIC"
strSql2 = strSql2 & ", ROUND(T_RATING/T_RATINGVOTES, 2) AS T_TOTALRATING"
strSql2 = strSql2 & " FROM ((" & strTablePrefix & "TRACKS TRACKS INNER JOIN " & strMemberTablePrefix & "MEMBERS MEMBERS ON TRACKS.T_MEMBERID=MEMBERS.MEMBER_ID) "
strSql2 = strSql2 & "INNER JOIN " & strTablePrefix & "TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID=REVIEWS.TR_TRACKID) "
strSql2 = strSql2 & "INNER JOIN " & strTablePrefix & "TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID=SIMULATION.TS_ID"
strSql2 = strSql2 & " WHERE T_SIMULATIONID = " & rs("TS_ID")
strSql2 = strSql2 & " AND T_UPLOADDATE > '" & strDateLow & "'"
strSql2 = strSql2 & " AND T_UPLOADDATE < '" & strDateHigh & "'"
strSql2 = strSql2 & " AND TR_DATE > '" & strDateLow & "'"
strSql2 = strSql2 & " AND TR_DATE < '" & strDateHigh & "'"
strSql2 = strSql2 & " GROUP BY T_ID"
strSql2 = strSql2 & ", T_MEMBERID"
strSql2 = strSql2 & ", M_NAME"
strSql2 = strSql2 & ", T_NAME"
strSql2 = strSql2 & ", T_FILE"
strSql2 = strSql2 & ", T_UPLOADDATE"
strSql2 = strSql2 & ", T_DOWNLOADED"
strSql2 = strSql2 & ", T_PICTHUMBPREVIEW"
strSql2 = strSql2 & ", T_SIMULATIONID"
strSql2 = strSql2 & ", TS_NAME"
strSql2 = strSql2 & ", TS_PIC"
strSql2 = strSql2 & ", T_RATING"
strSql2 = strSql2 & ", T_RATINGVOTES"
strSql2 = strSql2 & " ORDER BY T_RATING/T_RATINGVOTES desc"

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 15:15:22

is there anything i can do to help clarify what i am needing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 15:19:36

If you provided some sample data and what the result set should look like for the sample data, that would help. It's very hard to read dynamic sql, so if you just gave us an example of what strSql2 looks like instead, then that might help too. Also, what is it that you want to order by? T_TOTALRATING?

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 15:45:28

I have altered the SQL Statment to the following, but I am getting DUPE records displayed and the rating column is not calculated correctly. Here is a URL to view this on...

http://www.weeweeslap.com/track_exchange/admin_awards_new.asp

and the code


		strSql2 = "SELECT TOP 10 T_ID"
		strSql2 = strSql2 & ", T_MEMBERID"
		strSql2 = strSql2 & ", M_NAME"
		strSql2 = strSql2 & ", T_NAME"
		strSql2 = strSql2 & ", T_FILE"
		strSql2 = strSql2 & ", T_UPLOADDATE"
		strSql2 = strSql2 & ", T_DOWNLOADED"
		strSql2 = strSql2 & ", T_PICTHUMBPREVIEW"
		strSql2 = strSql2 & ", T_SIMULATIONID"
		strSql2 = strSql2 & ", TS_NAME"
		strSql2 = strSql2 & ", TS_PIC"
		strSql2 = strSql2 & ", ROUND((TR_TECHNICAL+TR_ADRENALINE+TR_ORIGINALITY/(3*COUNT(TR_ID))), 2) AS T_TOTALRATING"
		strSql2 = strSql2 & " FROM ((" & strTablePrefix & "TRACKS TRACKS INNER JOIN " & strMemberTablePrefix & "MEMBERS MEMBERS ON TRACKS.T_MEMBERID=MEMBERS.MEMBER_ID) "
		strSql2 = strSql2 & "INNER JOIN " & strTablePrefix & "TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID=REVIEWS.TR_TRACKID) "
		strSql2 = strSql2 & "INNER JOIN " & strTablePrefix & "TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID=SIMULATION.TS_ID"
		strSql2 = strSql2 & " WHERE T_SIMULATIONID = " & rs("TS_ID")
		strSql2 = strSql2 & " AND T_UPLOADDATE > '" & strDateLow & "'"
		strSql2 = strSql2 & " AND T_UPLOADDATE < '" & strDateHigh & "'"
		strSql2 = strSql2 & " AND TR_DATE > '" & strDateLow & "'"
		strSql2 = strSql2 & " AND TR_DATE < '" & strDateHigh & "'"
		strSql2 = strSql2 & " GROUP BY TRACKS.T_ID"
		strSql2 = strSql2 & ", T_MEMBERID"
		strSql2 = strSql2 & ", M_NAME"
		strSql2 = strSql2 & ", T_NAME"
		strSql2 = strSql2 & ", T_FILE"
		strSql2 = strSql2 & ", T_UPLOADDATE"
		strSql2 = strSql2 & ", T_DOWNLOADED"
		strSql2 = strSql2 & ", T_PICTHUMBPREVIEW"
		strSql2 = strSql2 & ", T_SIMULATIONID"
		strSql2 = strSql2 & ", TS_NAME"
		strSql2 = strSql2 & ", TS_PIC"
		strSql2 = strSql2 & ", TR_TECHNICAL"
		strSql2 = strSql2 & ", TR_ADRENALINE"
		strSql2 = strSql2 & ", TR_ORIGINALITY"
		strSql2 = strSql2 & ", TR_ID"
		strSql2 = strSql2 & " ORDER BY T_TOTALRATING desc"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 15:49:31

You are still showiong the dynamic sql. Could you show us one example of what strSql2 looks like after it is done being formed? I looked at the web site, but I'm not sure what I am looking at. What is it supposed to be ordering by? Just show us a few records and then show us how they should be ordered.

Also, why aren't you doing this inside a stored procedure?

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 16:27:22

Ive just never used stored procedures so thats why I am doing them this way.... Here is the SQL...

Here is some sample data and the SQL Script. Basically what I want is lets say return the top track record during a given date and the reviews between a given date that match the track. The dates are in long format where 20030216221043 would equal 02/16/2003 10:10:43 PM. Then it needs to grab the average rating which each review for the track would be calculated by (TR_TECHNICAL+TR_ADRENALINE+TR_ORIGINALITY)/3 (There is a 3 because of three ratings for each review. Does this help make since a little better?


SELECT     TOP 10 TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      ROUND(REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY / (3 * COUNT(REVIEWS.TR_ID)), 2) 
                      AS T_TOTALRATING
FROM         weeweeslap.FORUM_TRACKS TRACKS INNER JOIN
                      weeweeslap.FORUM_MEMBERS MEMBERS ON TRACKS.T_MEMBERID = MEMBERS.MEMBER_ID INNER JOIN
                      weeweeslap.FORUM_TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID = REVIEWS.TR_TRACKID INNER JOIN
                      weeweeslap.FORUM_TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID = SIMULATION.TS_ID
WHERE     (TRACKS.T_SIMULATIONID = 1) AND (TRACKS.T_UPLOADDATE > '20030824000001') AND (TRACKS.T_UPLOADDATE < '20030830235959') AND 
                      (REVIEWS.TR_DATE > '20030824000001') AND (REVIEWS.TR_DATE < '20030830235959')
GROUP BY TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      REVIEWS.TR_TECHNICAL, REVIEWS.TR_ADRENALINE, REVIEWS.TR_ORIGINALITY, REVIEWS.TR_ID
ORDER BY T_TOTALRATING DESC


FORUM_TRACKS
T_ID        T_MEMBERID  T_NAME                                             T_FILE                                             T_UPLOADDATE         T_DOWNLOADED T_PICTHUMBPREVIEW                                  T_SIMULATIONID 
----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------- ------------ -------------------------------------------------- -------------- 
62          2           XCR                                                2-20030216221043-t.nltrack                         20030216221043       55           2-20030216221043-st.jpg                            1
63          33          Jaguar                                             33-20030216221250-t.nltrack                        20030216221250       31           no_preview-st.jpg                                  1
64          2           Enter Sandman                                      2-20030216221416-t.nltrack                         20030216221416       46           2-20030216221416-st.jpg                            1
65          33          Gone In 60 Seconds                                 33-20030216221540-t.nltrack                        20030216221540       93           33-20030216221540-st.jpg                           1
66          33          King Coaster                                       33-20030216221655-t.nltrack                        20030216221655       57           33-20030216221655-st.jpg                           1

FORUM_TRACKS_REVIEWS
TR_ID       TR_TRACKID  TR_DATE              TR_TECHNICAL         TR_ADRENALINE        TR_ORIGINALITY       
----------- ----------- -------------------- -------------------- -------------------- -------------------- 
604         62          20030406135951       7.00                 6.00                 6.00
704         62          20030406136951       7.00                 6.00                 6.00
2541        65          20030715121858       8.50                 8.50                 9.00
2542        65          20030715121858       9.00                 9.00                 9.00
3218        66          20030811164240       8.00                 8.00                 8.00
3558        64          20030827005803       4.00                 7.50                 7.50

FORUM_MEMBERS
MEMBER_ID   M_NAME                                                                      
----------- --------------------------------------------------------------------------- 
2           Master_of_Puppets
33          G Force Addict


FORUM_TRACKS_SIMULATION
TS_ID       TS_NAME                                            TS_PIC                                             
----------- -------------------------------------------------- -------------------------------------------------- 
1           NoLimits Coaster                                   exchange/NL.gif

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 16:33:49

What does the result set look like with the query that you have now (using the sample data)? What should it look like? In other words, show us where the result set isn't correct.

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 16:44:19

You can see the output at http://www.weeweeslap.com/track_exchange/admin_awards_new.asp and its displaying duplicate track names/id's also the rating should never be above 10 if the calculations are done correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 17:07:35

Using your sample data and the query that you provided, I do not get any rows back. Here is the code to generate the tables and sample data (this will help other people answer your question):



SET NOCOUNT ON

CREATE TABLE FORUM_TRACKS
(
T_ID INT NOT NULL,
T_MEMBERID INT NOT NULL,
T_NAME VARCHAR(50) NOT NULL,
T_FILE VARCHAR(100) NOT NULL,
T_UPLOADDATE DATETIME NOT NULL,
T_DOWNLOADED INT NOT NULL, 
T_PICTHUMBPREVIEW VARCHAR(100) NOT NULL,
T_SIMULATIONID INT NOT NULL
)

CREATE TABLE FORUM_TRACKS_REVIEWS
(
TR_ID INT NOT NULL,
TR_TRACKID INT NOT NULL,
TR_DATE DATETIME NOT NULL,
TR_TECHNICAL DECIMAL(4, 2) NOT NULL,
TR_ADRENALINE DECIMAL(4, 2) NOT NULL,
TR_ORIGINALITY DECIMAL(4, 2) NOT NULL,
)

CREATE TABLE FORUM_MEMBERS
(
MEMBER_ID INT NOT NULL,
M_NAME VARCHAR(100) NOT NULL
)

CREATE TABLE FORUM_TRACKS_SIMULATION
(
TS_ID INT NOT NULL,
TS_NAME VARCHAR(100) NOT NULL,
TS_PIC VARCHAR(100)  NOT NULL
)

INSERT INTO FORUM_TRACKS VALUES(62, 2, 'XCR', '2-20030216221043-t.nltrack', 'Feb 16 2003 22:10:43', 55, '2-20030216221043-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(63, 33, 'Jaguar', '33-20030216221250-t.nltrack', 'Feb 16 2003 22:12:50', 31, 'no_preview-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(64, 2, 'Enter Sandman', '2-20030216221416-t.nltrack', 'Feb 16 2003 22:14:16', 46, '2-20030216221416-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(65, 33, 'Gone In 60 Seconds', '33-20030216221540-t.nltrack', 'Feb 16 2003 22:15:40', 93, '33-20030216221540-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(66, 33, 'King Coaster', '33-20030216221655-t.nltrack', 'Feb 16 2003 22:16:55', 57, '33-20030216221655-st.jpg', 1)

INSERT INTO FORUM_TRACKS_REVIEWS VALUES(604, 62, 'Apr 6 2003 13:59:51', 7.00, 6.00, 6.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(704, 62, 'Apr 6 2003 13:59:51', 7.00, 6.00, 6.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(2541, 65, 'July 15 2003 12:18:58', 8.50, 8.50, 9.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(2542, 65, 'July 15 2003 12:18:58', 9.00, 9.00, 9.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(3218, 66, 'Aug 11 2003 16:42:40', 8.00, 8.00, 8.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(3558, 64, 'Aug 27 2003 00:58:03', 4.00, 7.50, 7.50)

INSERT INTO FORUM_MEMBERS VALUES(2, 'Master_of_Puppets')
INSERT INTO FORUM_MEMBERS VALUES(33, 'G Force Addict')

INSERT INTO FORUM_TRACKS_SIMULATION VALUES(1, 'NoLimits Coaster', 'exchange/NL.gif')

SELECT     TOP 10 TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      ROUND(REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY / (3 * COUNT(REVIEWS.TR_ID)), 2) 
                      AS T_TOTALRATING
FROM         FORUM_TRACKS TRACKS INNER JOIN
                      FORUM_MEMBERS MEMBERS ON TRACKS.T_MEMBERID = MEMBERS.MEMBER_ID INNER JOIN
                      FORUM_TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID = REVIEWS.TR_TRACKID INNER JOIN
                      FORUM_TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID = SIMULATION.TS_ID
WHERE     (TRACKS.T_SIMULATIONID = 1) AND (TRACKS.T_UPLOADDATE > 'Aug 24 2003 00:00:01') AND (TRACKS.T_UPLOADDATE < 'Aug 30 2003 23:59:59') AND 
                      (REVIEWS.TR_DATE > 'Aug 24 2003 00:00:01') AND (REVIEWS.TR_DATE < 'Aug 30 2003 23:59:59')
GROUP BY TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      REVIEWS.TR_TECHNICAL, REVIEWS.TR_ADRENALINE, REVIEWS.TR_ORIGINALITY, REVIEWS.TR_ID
ORDER BY T_TOTALRATING DESC


DROP TABLE FORUM_TRACKS
DROP TABLE FORUM_TRACKS_REVIEWS
DROP TABLE FORUM_MEMBERS
DROP TABLE FORUM_TRACKS_SIMULATION

If you change the sample data, please provide it in the same form as I have.

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 17:14:29

Tara the reason yours did not return any records is because no records matched the date critia (which is my fault) I have change the dates and it should return some data


SET NOCOUNT ON

CREATE TABLE FORUM_TRACKS
(
T_ID INT NOT NULL,
T_MEMBERID INT NOT NULL,
T_NAME VARCHAR(50) NOT NULL,
T_FILE VARCHAR(100) NOT NULL,
T_UPLOADDATE DATETIME NOT NULL,
T_DOWNLOADED INT NOT NULL, 
T_PICTHUMBPREVIEW VARCHAR(100) NOT NULL,
T_SIMULATIONID INT NOT NULL
)

CREATE TABLE FORUM_TRACKS_REVIEWS
(
TR_ID INT NOT NULL,
TR_TRACKID INT NOT NULL,
TR_DATE DATETIME NOT NULL,
TR_TECHNICAL DECIMAL(4, 2) NOT NULL,
TR_ADRENALINE DECIMAL(4, 2) NOT NULL,
TR_ORIGINALITY DECIMAL(4, 2) NOT NULL,
)

CREATE TABLE FORUM_MEMBERS
(
MEMBER_ID INT NOT NULL,
M_NAME VARCHAR(100) NOT NULL
)

CREATE TABLE FORUM_TRACKS_SIMULATION
(
TS_ID INT NOT NULL,
TS_NAME VARCHAR(100) NOT NULL,
TS_PIC VARCHAR(100)  NOT NULL
)

INSERT INTO FORUM_TRACKS VALUES(62, 2, 'XCR', '2-20030216221043-t.nltrack', 'Aug 25 2003 22:10:43', 55, '2-20030216221043-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(63, 33, 'Jaguar', '33-20030216221250-t.nltrack', 'Aug 26 2003 22:12:50', 31, 'no_preview-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(64, 2, 'Enter Sandman', '2-20030216221416-t.nltrack', 'Aug 27 2003 22:14:16', 46, '2-20030216221416-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(65, 33, 'Gone In 60 Seconds', '33-20030216221540-t.nltrack', 'Aug 28 2003 22:15:40', 93, '33-20030216221540-st.jpg', 1)
INSERT INTO FORUM_TRACKS VALUES(66, 33, 'King Coaster', '33-20030216221655-t.nltrack', 'Aug 27 2003 22:16:55', 57, '33-20030216221655-st.jpg', 1)

INSERT INTO FORUM_TRACKS_REVIEWS VALUES(604, 62, 'Aug 25 2003 13:59:51', 7.00, 6.00, 6.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(704, 62, 'Aug 25 2003 13:59:51', 7.00, 6.00, 6.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(2541, 65, 'Aug 26 2003 12:18:58', 8.50, 8.50, 9.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(2542, 65, 'Aug 27 2003 12:18:58', 9.00, 9.00, 9.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(3218, 66, 'Aug 25 2003 16:42:40', 8.00, 8.00, 8.00)
INSERT INTO FORUM_TRACKS_REVIEWS VALUES(3558, 64, 'Aug 28 2003 00:58:03', 4.00, 7.50, 7.50)

INSERT INTO FORUM_MEMBERS VALUES(2, 'Master_of_Puppets')
INSERT INTO FORUM_MEMBERS VALUES(33, 'G Force Addict')

INSERT INTO FORUM_TRACKS_SIMULATION VALUES(1, 'NoLimits Coaster', 'exchange/NL.gif')

SELECT     TOP 10 TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      ROUND(REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY / (3 * COUNT(REVIEWS.TR_ID)), 2) 
                      AS T_TOTALRATING
FROM         FORUM_TRACKS TRACKS INNER JOIN
                      FORUM_MEMBERS MEMBERS ON TRACKS.T_MEMBERID = MEMBERS.MEMBER_ID INNER JOIN
                      FORUM_TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID = REVIEWS.TR_TRACKID INNER JOIN
                      FORUM_TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID = SIMULATION.TS_ID
WHERE     (TRACKS.T_SIMULATIONID = 1) AND (TRACKS.T_UPLOADDATE > 'Aug 24 2003 00:00:01') AND (TRACKS.T_UPLOADDATE < 'Aug 30 2003 23:59:59') AND 
                      (REVIEWS.TR_DATE > 'Aug 24 2003 00:00:01') AND (REVIEWS.TR_DATE < 'Aug 30 2003 23:59:59')
GROUP BY TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      REVIEWS.TR_TECHNICAL, REVIEWS.TR_ADRENALINE, REVIEWS.TR_ORIGINALITY, REVIEWS.TR_ID
ORDER BY T_TOTALRATING DESC


DROP TABLE FORUM_TRACKS
DROP TABLE FORUM_TRACKS_REVIEWS
DROP TABLE FORUM_MEMBERS
DROP TABLE FORUM_TRACKS_SIMULATION

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 17:31:28

I don't think that T_TOTALRATING is being calculated correctly.

Here is what I changed it to:
ROUND(((REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY)/3), 2)

Is that correct? Run the SELECT and with the modification and let me know if T_TOTALRATING is correct now. If it isn't, let me know what is wrong (I don't understand why you had the COUNT part in it). If it is correct, what else is wrong with this result set that is returned?

I'll keep looking at it to see if I can spot anything else.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 17:35:08

I changed the SELECT statement so that it is more readable (and has fewer columns):


SELECT TOP 10 MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_PICTHUMBPREVIEW, 
       ROUND(((REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY)/3), 2) AS T_TOTALRATING
FROM FORUM_TRACKS TRACKS 
INNER JOIN FORUM_MEMBERS MEMBERS ON TRACKS.T_MEMBERID = MEMBERS.MEMBER_ID
INNER JOIN FORUM_TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID = REVIEWS.TR_TRACKID 
INNER JOIN FORUM_TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID = SIMULATION.TS_ID
WHERE     (TRACKS.T_SIMULATIONID = 1) AND (TRACKS.T_UPLOADDATE > 'Aug 24 2003 00:00:01') AND (TRACKS.T_UPLOADDATE < 'Aug 30 2003 23:59:59') AND 
                      (REVIEWS.TR_DATE > 'Aug 24 2003 00:00:01') AND (REVIEWS.TR_DATE < 'Aug 30 2003 23:59:59')
GROUP BY TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      REVIEWS.TR_TECHNICAL, REVIEWS.TR_ADRENALINE, REVIEWS.TR_ORIGINALITY, REVIEWS.TR_ID
ORDER BY T_TOTALRATING DESC

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 17:45:19

Tara,

Thanks so much for your help so far. The reason I had the count is lets say track #1 had 5 reviews which each part of the review gave it a 5 so the total rating for 1 review would be 15/3 = 5. Like I said if all 5 reviews gave it a 5, wouldnt ROUND(((REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY)/3), 2) return..
((5+5+5) + (5+5+5) + (5+5+5) + (5+5+5) + (5+5+5))/3 which it should be devided by the number of reviews for each track multiply by 3 since there are three reviews for each review. Does that make since?

I tried the query you just posted and it returned Gone In 60 Seconds twice which the tracks should only be shown once with all the reviews for that track in the given time period grouped together.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 17:58:58

The calculation should be ROUND((((REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY)/3), 2))/<number of reviews>

The reason why Gone In 60 Seconds in shown twice (XCR is as well) is because there are two reviews.
So my query doesn't account for multiple reviews.

I don't know how to fix your problem, so I am going to give some information for the next person to help you out:

Here is what the result set should look like (I think at least):


M_NAME                                                                                               T_NAME                                             T_FILE                                                                                               T_PICTHUMBPREVIEW                                                                                    T_TOTALRATING 
---------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------- 
G Force Addict                                                                                       Gone In 60 Seconds                                 33-20030216221540-t.nltrack                                                                          33-20030216221540-st.jpg                                                                             8.840000
G Force Addict                                                                                       King Coaster                                       33-20030216221655-t.nltrack                                                                          33-20030216221655-st.jpg                                                                             8.000000
Master_of_Puppets                                                                                    Enter Sandman                                      2-20030216221416-t.nltrack                                                                           2-20030216221416-st.jpg                                                                              6.330000
Master_of_Puppets                                                                                    XCR                                                2-20030216221043-t.nltrack                                                                           2-20030216221043-st.jpg                                                                              6.330000

Can someone show us what needs to be done to fix this, cuz I'm stuck?

The difference is in the last column.

The last column represents the T_TOTALRATING/(number of reviews).
In this case, the number of reviews is 1 for all of them except XCR and Gone In 60 Seconds, which is 2.

Tara

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-08-28 : 18:52:41

could another sql statment help? one that is just used to count the reviews?

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-09-01 : 19:27:55

Anyone else have any ideas on this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-01 : 19:49:36

does this help:

select SomeGroup, sum(value1 + value2 + value3 ) / count(value) as Rating
from
table
group by SomeGroup
order by sum(value1+ value2+value3) / count(value)

that gives you the average of (value1 + value2 + value3) for each "SomeGroup" in the table.

and of course you can order that same expression as well.

- Jeff

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-09-01 : 20:05:42

Well the only problem I am having now is that it returning duplicate tracks because the tracks have more then one review so for each review it returns the track an additional time. I thought the Group By would of helped but it didn't. Do you have any other ideas?


SELECT     TOP 10 TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      ROUND(REVIEWS.TR_TECHNICAL + REVIEWS.TR_ADRENALINE + REVIEWS.TR_ORIGINALITY / (3 * COUNT(REVIEWS.TR_ID)), 2) 
                      AS T_TOTALRATING
FROM         FORUM_TRACKS TRACKS INNER JOIN
                      FORUM_MEMBERS MEMBERS ON TRACKS.T_MEMBERID = MEMBERS.MEMBER_ID INNER JOIN
                      FORUM_TRACKS_REVIEWS REVIEWS ON TRACKS.T_ID = REVIEWS.TR_TRACKID INNER JOIN
                      FORUM_TRACKS_SIMULATION SIMULATION ON TRACKS.T_SIMULATIONID = SIMULATION.TS_ID
WHERE     (TRACKS.T_SIMULATIONID = 1) AND (TRACKS.T_UPLOADDATE > '20030831000001') AND (TRACKS.T_UPLOADDATE < '20030906235959') AND 
                      (REVIEWS.TR_DATE > '20030831000001') AND (REVIEWS.TR_DATE < '20030906235959')
GROUP BY TRACKS.T_ID, TRACKS.T_MEMBERID, MEMBERS.M_NAME, TRACKS.T_NAME, TRACKS.T_FILE, TRACKS.T_UPLOADDATE, 
                      TRACKS.T_DOWNLOADED, TRACKS.T_PICTHUMBPREVIEW, TRACKS.T_SIMULATIONID, SIMULATION.TS_NAME, SIMULATION.TS_PIC, 
                      REVIEWS.TR_TECHNICAL, REVIEWS.TR_ADRENALINE, REVIEWS.TR_ORIGINALITY, REVIEWS.TR_ID
ORDER BY T_TOTALRATING DESC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-01 : 20:17:33

You are grouping by too many fields, and you are NOT using aggregate functions. That is why duplicates are returned. DON'T just add fields to your GROUP BY clause until the errors go away. Try to understand how aggregates work and use small, simple sets of data until you get the hang of it.

look at this:

create table Foo (Track int, Rating money)

insert into Foo
select 'Boston', 5 union
select 'Boston', 4 union
select 'NY', 3 union
select 'NY', 5 union
select 'LA', 2 union
select 'LA', 1 union
select 'LA', 5 union
select 'Chicago', 4

select * from Foo

select Track, Rating
from Foo
group by Track, Rating

select Track, SUM(Rating) as totalRating
from Foo
Group by Track

select Track, SUM(Rating) as TotalRating, COUNT(*) as [Number of Rating], SUM(Rating)/Count(*) as AverageRating
from Foo
GROUP BY Track

Make sure you COMPLETELY understand all of the above, and why the results are returned, before trying to tackle your larger problem.

Then, try to put it all together.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-01 : 20:22:00

further info:

As mentioned, DON'T try to do too much at once! what are you trying to return? how can you do it step by step?

look at this:

selec t_id,
sum(tr_technical + tr_Adrenaline + tr_Originality) / (3.0 * count(*)) as Score
from
reviews
group by t_id

now, instead of joining to the "Reviews" table, join to this "subquery" instead. There's an article on derived tables somewhere here at SQL-Team.

- Jeff

redbrad0
Posting Yak Master

176 Posts

Posted - 2003-09-01 : 20:25:44

But if I am just pulling a bunch of different columns and not adding, counting the column I see there is a VAR(column) function should I use these on the different tables? Can you point me somewhere?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources