|
How to do this using SQL-DMO? Subscriber and publisher status is needed.
I keep submitting “VB” as a Priority-1 bug, but apparently no one here knows how to fix it. Nick Hodapp, Semicolon
|
|
|
|
|
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
|
|
|
|