Help with a View - 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

General SQL Server Forums

New to SQL Server Programming

Help with a View

Author

Topic

njguy
Starting Member

16 Posts

Posted - 2012-05-07 : 16:37:51

I have two tables

T1 IdeaSeq
Description

T2 RateSeq
IdeaSeq
Rating1
Rating2
Rating3
Rating4

T1 Parent Table and T2 Child Table

My view needs to show the two fields from T1 and the average of each ranking (there are probably 10 child records for every T1 record)

I have no idea how to start

thanks

Tom

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-05-07 : 16:48:48

Did you mean: average of each ~~ranking~~ rating?
If so - perhaps this:


create view dbo.myView
as
select t1.IdeaSeq
       ,t1.Description
       ,avg(t2.Rating1) as Rating1Average
       ,avg(t2.Rating2) as Rating2Average
       ,avg(t2.Rating3) as Rating3Average
       ,avg(t2.Rating4) as Rating4Average
from t1
join t2 on t2.IdeaSeq = t1.IdeaSeq

go

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 16:55:06

Can you show some sample data please? Cant make out much from posted structure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

njguy
Starting Member

16 Posts

Posted - 2012-05-07 : 17:47:43

quote:

Originally posted by TG

Did you mean: average of each ~~ranking~~ rating?
If so - perhaps this:


create view dbo.myView
as
select t1.IdeaSeq
       ,t1.Description
       ,avg(t2.Rating1) as Rating1Average
       ,avg(t2.Rating2) as Rating2Average
       ,avg(t2.Rating3) as Rating3Average
       ,avg(t2.Rating4) as Rating4Average
from t1
join t2 on t2.IdeaSeq = t1.IdeaSeq

go

Be One with the Optimizer
TG

I will give it a try

njguy
Starting Member

16 Posts

Posted - 2012-05-07 : 17:54:31

quote:
Originally posted by visakh16

Can you show some sample data please? Cant make out much from posted structure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Table (T1)
IdeaSeq Description
10 Coffee Table
20 Chair

Table(T2)
RateSeq IdeaSeq Rating1 Rating2 Rating3 Rating4
1 10 5 3 5 4
2 10 5 5 1 3

Result Table
IdeaSeq Description Rating1 Rating2 Rating3 Rating4
10 Coffee Table 5 4 3 3.5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 19:41:46

[code]
SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
AVG(Rating1*1.0) AS Rating1,
AVG(Rating2*1.0) AS Rating2,
AVG(Rating3*1.0) AS Rating3,
AVG(Rating4*1.0) AS Rating4
FROM T2
GROUP BY IdeaSeq
)t2
ON t2.IdeaSeq = t1.IdeaSeq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

njguy
Starting Member

16 Posts

Posted - 2012-05-07 : 20:07:24

quote:

Originally posted by visakh16


SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
            AVG(Rating1*1.0) AS Rating1,
            AVG(Rating2*1.0) AS Rating2,
            AVG(Rating3*1.0) AS Rating3,
            AVG(Rating4*1.0) AS Rating4
            FROM T2
            GROUP BY IdeaSeq
           )t2
ON t2.IdeaSeq = t1.IdeaSeq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GREAT....

One last question...they have requirment that if a rating is set to 0 (which is a valid entry)to not include it in the calculation for that rating.

For example

Rating1 Rating2
5 5
0 2
3 3

The Average for Rating 1 would be 4 not 2.6 but the Average for Rating 2 would be 3.33

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 22:28:41

[code]
SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1,
AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2,
AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3,
AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4
FROM T2
GROUP BY IdeaSeq
)t2
ON t2.IdeaSeq = t1.IdeaSeq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

njguy
Starting Member

16 Posts

Posted - 2012-05-08 : 09:27:16

quote:

Originally posted by visakh16


SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
            AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1,
            AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2,
            AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3,
            AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4
            FROM T2
            GROUP BY IdeaSeq
           )t2
ON t2.IdeaSeq = t1.IdeaSeq

How could I sum up the 4 Rating columns to show as a seperate column....Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:20:10

you mean sum the averages or sum up values? if former, whats the significance of that? if latter you can use below


SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4,t2.TotalRating
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
            AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1,
            AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2,
            AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3,
            AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4,
            SUM(Rating1 + Rating2 + Rating3 + Rating4) AS TotalRating
            FROM T2
            GROUP BY IdeaSeq
           )t2
ON t2.IdeaSeq = t1.IdeaSeq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

njguy
Starting Member

16 Posts

Posted - 2012-05-08 : 11:40:45

quote:

Originally posted by visakh16

you mean sum the averages or sum up values? if former, whats the significance of that? if latter you can use below


SELECT t1.IdeaSeq,t1.Description,t2.Rating1,t2.Rating2,t2.rating3,t2.Rating4,t2.TotalRating
FROM T1 t1
INNER JOIN (SELECT IdeaSeq,
            AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1,
            AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2,
            AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3,
            AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4,
            SUM(Rating1 + Rating2 + Rating3 + Rating4) AS TotalRating
            FROM T2
            GROUP BY IdeaSeq
           )t2
ON t2.IdeaSeq = t1.IdeaSeq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CREATE VIEW dbo.IdeasResultsView
AS
SELECT Ideas.IdeaSeq,Ideas.Refrence,Ideas.BriefDescription,Rating1Avg,Rating2Avg,Rating3Avg,Rating4Avg,RatingSum
FROM NewIdeas.dbo.Ideas
INNER JOIN (SELECT IdeaSeq,
AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1Avg,
AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2Avg,
AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3Avg,
AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4Avg,
SUM(Rating1Avg + Rating2Avg + Rating3Avg + Rating4Avg)AS RatingSum
FROM NewIdeas.dbo.Ratings
GROUP BY IdeaSeq
) Ratings

ON Ratings.IdeaSeq = Ideas.IdeaSeq
WHERE Ideas.Approved = 'Y'

visakh....I am getting invalid column name on the Sum line....Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:46:06

why do you need to sum the averages? i dont think so it will have significance. It might be that you're look at AVerage of sum of ratings.

Anyways here you go with your requirement


CREATE VIEW dbo.IdeasResultsView
AS
SELECT Ideas.IdeaSeq,Ideas.Refrence,Ideas.BriefDescription,Rating1Avg,Rating2Avg,Rating3Avg,Rating4Avg,
Rating1Avg + Rating2Avg + Rating3Avg + Rating4Avg AS RatingSum
FROM NewIdeas.dbo.Ideas 
INNER JOIN (SELECT IdeaSeq,
AVG(CASE WHEN Rating1 > 0 THEN Rating1*1.0 END) AS Rating1Avg,
AVG(CASE WHEN Rating2 > 0 THEN Rating2*1.0 END) AS Rating2Avg,
AVG(CASE WHEN Rating3 > 0 THEN Rating3*1.0 END) AS Rating3Avg,
AVG(CASE WHEN Rating4 > 0 THEN Rating4*1.0 END) AS Rating4Avg
FROM NewIdeas.dbo.Ratings
GROUP BY IdeaSeq
) Ratings
ON Ratings.IdeaSeq = Ideas.IdeaSeq
WHERE Ideas.Approved = 'Y'

I'm definitely not sure whether this is exactly want you need to return

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources