|
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
|
|
|
|
|
If the 'word' column is indexed as unique, SQL Server will not have to look through the entire table and will only need to search the index (very fast) to find the desired row.
If the 'word' column is indexed as non-unique, SQL server will still search the index, but may need to return more than one row.
If the 'word' column is not indexed SQL server will do a table-scan (full table search) and return every match.
You can use the SELECT TOP 1 bla.bla.bla syntax, but probably what you want is for SQL Server to have a unique index on the 'word' column.
|
|
|
|
|
If you're using SQL Server you can use Query Analyzer to find out how long it took to run the query, and where the greatest cost was incurred. It's an incredibly useful tool.
--
Paul
"I need the secure packaging of Jockeys. My boys need a house!"
- Kramer, in "The Chinese Woman" episode of Seinfeld
MS Messenger: paul@oobaloo.co.uk
Sonork: 100.22446
|
|
|
|
|
DISTINCT is in general slow (depends on actual data of course). Whenever you use DISTINCT, SQL server needs to group result set (by sorting them) on DISTINCT columns. In case there's no index on those columns, it will take time. On the other hand, if result set is huge, but it contains a lot of repetitions on DISTINCT columns, eliminating repetitions may have positive effect on speed of transferring filtered results to the client.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
When you run two concurrent-ish updates through Jet from different users of the same program, there seems to be a significant (~5 secs) delay before either one can see the other's update.
This, as I understand it, is a delay in the Jet drivers before updates are actually flushed to the database. Is this right?
If so, is there any way to force a flush without the performace hit of stop-starting the connection (pref. using ADO)?
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
try setting the following connection property:
.Properties("Jet OLEDB:Implicit Commit Sync") = True 'force synchronous write
|
|
|
|