|
Hi all
I'm not sure what todo....I think I need a function...
This is my problem: I have a table called [References] with a [Rating] (tinyint) field. This field can be NULL or be 1,2,3,4,5 (stars ). Now what I wanna do is get an average of all non-NULL data where the [PersonID] (bigint) field equals a value from a SELECT query. OK I think I need to visualize
Table [References]:
[ID] bigint, (PK)
[PersonID] bigint,
[Rating] tinyint
Table [Users]:
[ID] bigint, (PK)
[Name] varchar(50),
[Role] int
Now I want the data to displayed as follows:
Output Table:
[Users.Name],
[References.AvgRating] ??????
where say [Role] = x
AS you can see I'm clueless but please help me
Cheers
"There are no stupid question's, just stupid people."
|
|
|
|
|
AVG is what you're looking for.
Get the statement w/all the joins together, and just select AVG([Rating]) out of it.
"The greatest danger to humanity is humanity without an open mind." - Ian Mariano
http://www.ian-space.com/
|
|
|
|
|
Thanx, I actually had a problem in the DB (wasnt set to allow NULLs (make mental note: stop coding drunk)). Anyways it does work, on its own, but in a more complex statement it doesnt... Well, actually 1 value is returned but no others...
Cheers
"There are no stupid question's, just stupid people."
|
|
|
|
|
Sometimes you can't help imbibing a little, especially when dealing with OLAP, and the vaporous nature of what data a financial analyst client who knows just enough about OLAP really wants;P
"The greatest danger to humanity is humanity without an open mind." - Ian Mariano
http://www.ian-space.com/
|
|
|
|
|
This should work:
CREATE PROC sp_GetAvgRatings (@Role int)
AS
SELECT
U.[Name],
AVG(R.Rating) As AvgRating
FROM
(
-- Filter for role
SELECT
ID,
[Name]
FROM
Users
WHERE
(Role = @Role OR @Role Is Null)
) As U
INNER JOIN
(
-- Get rid of null values
SELECT
PersonID,
Rating
FROM
References
WHERE
NOT (Rating Is Null)
) As R
ON U.ID = R.PersonID
GROUP BY
U.[Name]
|
|
|
|
|
Eh thanks I actually found the problem
It was the SQL editor creating "automatic" joins and links and whatever, so I removed then, took it step by step, recombined them like I wanted it and it worked
Cheers
"There are no stupid question's, just stupid people."
|
|
|
|
|
Are there any SQL Server books for programmers that stand out among the croud?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
This does depend a bit upon what you want to learn, but the best "SQL Server Book" IMHO is Inside SQL Server 2000 (Delaney).
The book discusses SQL Server in great detail, but it does not offer any information regarding ADO / ADO.NET er other middle layer / access technologies.
Morty
|
|
|
|
|
I know the middle layer stuff. I guess I just need something that does a good job brining you up to speed quickly on the details of SQL Server as a database. I'm not a DBA and don't intend to be, but I really am in need of understanding stored procedures for my current job.
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Then that book is great. It will teach you all you need to know (from a developers point of view) about SQL Server.
It covers SQL Server stored procedure programming, security systems, query optimization, indexing internals, best practices and so on. It provides you with the knowledge you need to make educated decisions, but it is by NO MEANS a learn by example kinda of book.
Morty
|
|
|
|
|
Sounds great!
Thanks for your time.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Yes, try this one: "SQL Server 2000 with Visual Basic .NET" from Microsoft Press.
|
|
|
|
|
SELECT Company FROM Orders
-or-
SELECT DISTINCT Company FROM Orders
Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways?
Thanks guys!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
SELECT Company FROM Orders will list every record, IOW
ABC
XYZ
ABC
EFG
GHI
XYZ
SELECT DISTINCT Company FROM Orders will list only unique matches IOW
ABC
XYZ
EFG
GHI
Have a look at the SQL Server SELECT statement article, nice
"There are no stupid question's, just stupid people."
|
|
|
|
|
Thanks!
I guess the real question is how to prevent the complete parsing of the
records. Is there a way to tell SQL to "find the first occurence then stop"?
Luc
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Well, this depends on your data.
Speaking from a purely SQL Server perspective the basic SELECT is the fastest, but if the DISTINCT clause eliminates a large amount of records it might be faster when taking the middle tier, network access, aso (basically the rest of your processing...) into consideration.
The thing to remember is that a DISTINCT is post processed. So the SQL Server actually makes a complete SELECT first, and then filters the records.
Morty
|
|
|
|
|
Here's what I want to do:
I have a table that contains words and there are no duplicates. Right now,
this is what I do:
SELECT * FROM table WHERE word = 'hello';
This will cause the SQL server to parse the entire table to look for records
that match the criteria. But since I'm 100% sure that the word 'hello' is
only there once in the table, I want the query to stop as soon as it finds
the record.
What can I use for this kind of job?
Thanks for your help!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
What can I use for this kind of job?
I think what you're looking for is "SELECT TOP 1" but I'm not an expert. Give it a go.
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|