View question. - 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

Transact-SQL (2000)

View question.

Author

Topic

RobVG
Starting Member

42 Posts

Posted - 2003-07-28 : 13:21:30

My first view and I'm confused.

CREATE VIEW RadioStats AS 
SELECT COUNT (p.ProgEval_ID)  EvalCount, COUNT(DISTINCT r.ProgEval_ID) RadioCount
FROM ProgramEval p, Radio r
WHERE r.ProgEval_ID = p.ProgEval_ID

I jutst want to take the distinct count of "r.ProgEval_ID" (an Fk in "Radio" referencing ProgramEval-ProgEval_ID) and divide it the total number of records in ProgramEval.

Where its stands now, I get the correct distict RadioCount but then it returns the number of records in RadiCount, Not ProgramEval.

Even If I(we,you

) can get this statement to work where do you put

((RadioCount/EvalCount)*100) RadioStat

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-28 : 13:32:22

quote:
Originally posted by RobVG

My first view and I'm confused.

CREATE VIEW RadioStats AS
SELECT COUNT (p.ProgEval_ID) EvalCount, COUNT(DISTINCT r.ProgEval_ID) RadioCount
FROM ProgramEval p, Radio r
WHERE r.ProgEval_ID = p.ProgEval_ID

I jutst want to take the distinct count of "r.ProgEval_ID" (an Fk in "Radio" referencing ProgramEval-ProgEval_ID) and divide it the total number of records in ProgramEval.

Where its stands now, I get the correct distict RadioCount but then it returns the number of records in RadiCount, Not ProgramEval.

Even If I(we,you) can get this statement to work where do you put
((RadioCount/EvalCount)*100) RadioStat

Why are you joining the 2? You need to rember SQL is a set processing kind of thing. Once you make that join, that's what you'll get.

How about:



SELECT EvalCount, RadioCount, (RadioCount/EvalCount)*100 AS RadioStat 
FROM	  (SELECT COUNT(DISTINCT ProgEval_ID) AS RadioCount FROM Radio) AS A
	, (SELECT COUNT(ProgEval_ID) AS RadioCount FROM RadioProgramEval) AS B

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-28 : 13:32:43

Forget about the view, just put the SELECT statement in Query Analyzer. Once you ge the SELECT statement to work correctly, then you build the view.

Why don't you do this instead:

CREATE VIEW RadioStats AS
SELECT ((COUNT(p.ProgEval_ID)/COUNT(r.ProgEval_ID))*100) AS RadioStat
FROM ProgramEval p, Radio r
WHERE r.ProgEval_ID = p.ProgEval_ID

I don't quite get what you are asking though, but at least you can see how to do this in one step.

Tara

RobVG
Starting Member

42 Posts

Posted - 2003-07-28 : 13:45:13

I didn't want to use a join but I failed to find an example like Brett's on how to nest the SELECT statements.

Thanks!

Rob

RobVG
Starting Member

42 Posts

Posted - 2003-07-28 : 15:43:08

SELECT EvalCount, RadioCount, (RadioCount/EvalCount) * 100 AS RadioStat
FROM	  (SELECT COUNT(DISTINCT ProgEval_ID) AS RadioCount FROM Radio)AS A,
	  (SELECT COUNT(ProgEval_ID) AS EvalCount FROM ProgramEval)AS B

Hmmm...

"RadioCount" and "EvalCount" work fine.

Do I need to assing something like a floating pt. datatype to "RadioStat" (?) because it only returns a '0' unless RadioCount = Evalcount in which case it's value is 100.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-28 : 16:14:51

Yes, you can cast either RadioCount or EvalCount to float:
(CAST(RadioCount AS float)/EvalCount) * 100

RobVG
Starting Member

42 Posts

Posted - 2003-07-28 : 16:39:51

Thanks Arnold.

I hadn't gotten very far with

(10*RadioCount/EvalCount)*10

RobVG
Starting Member

42 Posts

Posted - 2003-07-29 : 16:36:15

"Yesterday, All my troubles seemed so far away..."

You guy's really helped me with creating this view:


CREATE VIEW RadioStats AS 
SELECT EvalCount, RadioCount,CAST( ROUND((CAST(RadioCount AS float)/EvalCount * 100),2)AS DECIMAL(4,2)) AS [%Submited- RadListeners]
FROM	  (SELECT COUNT(DISTINCT ProgEval_ID) AS RadioCount FROM Radio)AS A,
	  (SELECT COUNT(ProgEval_ID) AS EvalCount FROM ProgramEval)AS B

Given your example, I'm trying to add this Column to it and I think I tried every possible combination of imaginable syntax with no luck.

SELECT    CAST (ROUND((CAST(BuzzCount AS Float)/(TotalStations ) *100),1)AS DECIMAL(4,2)) AS [%"The Buzz"]
FROM	  (SELECT MAX(Rad_ID) AS TotalStations FROM Radio)AS A,
	  (SELECT COUNT(StationName)AS BuzzCount FROM Radio WHERE StationName = '"The Buzz" 100.7 FM' ) AS B

What is the "AS A", "AS B"- some kind of place holder?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-29 : 16:39:59

The AS statement (it is optional BTW) is a rowset alias. It's used to save typing and make DML statements easier to read. I always use them when a DML statement references more than 1 table and I highly encourage you to do the same.

Jonathan
{0}

RobVG
Starting Member

42 Posts

Posted - 2003-07-30 : 02:27:39

OK Jonathan, I guess I was getting a little spoiled.

I remembered "design view" in EM and was able to see the structure a little better.

CREATE VIEW RadioStats AS 
SELECT     B.EvalCount, A.RadioCount,
           CAST(ROUND(CAST(A.RadioCount AS float) / B.EvalCount * 100, 2) AS DECIMAL(4, 2)) AS [%Submited- RadListeners],
           CAST(ROUND((CAST(C.BuzzCount AS Float)/(D.TotalStations ) *100),1)AS DECIMAL(4,2)) AS [%"The Buzz"]

FROM         (SELECT     COUNT(DISTINCT ProgEval_ID) AS RadioCount
               FROM        Radio)AS A,
                (SELECT     COUNT(ProgEval_ID) AS EvalCount
                  FROM        ProgramEval) AS B,
		   (SELECT     COUNT(StationName) AS BuzzCount
                      FROM      Radio WHERE StationName = '"The Buzz" 100.7 FM') AS C,
                       (SELECT   MAX(Rad_ID) AS TotalStations
		          FROM Radio) AS D

(Funny[to me], EM shows a Cross Join betweem A and B, C and D)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 09:30:07

quote:
Originally posted by setbasedisthetruepath

The AS statement (it is optional BTW) is a rowset alias.

Not in a derived table I don't believe...

quote:

It's used to save typing and make DML statements easier to read. I always use them when a DML statement references more than 1 table and I highly encourage you to do the same.

Yes absolutley

But it's a matter of prefernce.

Which do you prefer?


USE Northwind
GO

SELECT TOP 10 * 
      FROM Orders 
INNER JOIN [Order Details] 
        ON Orders.OrderId = [Order Details].OrderId
GO

SELECT TOP 10 * 
      FROM Orders l 
INNER JOIN [Order Details] r 
        ON l.OrderId = r.OrderId
GO

Brett

8-)

Rob: What is this for btw? [%"The Buzz"]

Wish I had one right now...

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-30 : 10:12:39

The AS (literally) is optional.

So: (rowset) A is the same as (rowset) AS A. Same for columns.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 10:23:14

quote:
Originally posted by setbasedisthetruepath

The AS (literally) is optional.

So: (rowset) A is the same as (rowset) AS A. Same for columns.

Jonathan
{0}

[homer] doooooh [/homer]

Of course!

I got confused with yje need to alias columns...


SELECT TOP 10 UnitPrice+Freight 
      FROM Orders l 
INNER JOIN [Order Details] r 
        ON l.OrderId = r.OrderId
GO

Synatx that I saw once that I thought was wrong, but isn't, is the = assignment


SELECT TOP 10 TotalCost=UnitPrice+Freight
	, UnitPrice+Freight AS TOT_COST 
	, UnitPrice+Freight FullCost 
      FROM Orders l 
INNER JOIN [Order Details] r 
        ON l.OrderId = r.OrderId
GO

Brett

8-)

RobVG
Starting Member

42 Posts

Posted - 2003-07-30 : 14:21:50

quote:
Rob: What is this for btw? [%"The Buzz"]
Wish I had one right now...

I concur.

The VB app is a questionnaire. The db holds the answers.
The school I’m working for (honorary intern) does some radio advertising. [%"The Buzz"] and other columns ([%KOMO]) will break down the percentage of students who heard about the school on the radio and give the percentage of the total for each station.

Unless, of course, ive SU'd somewhere?

One last thing. (Yeah...right)

Is it possible to conditionally implement (?) a view i.e. have sql pass over the select statement for "TheBuzz" if there are no entries for "The Buzz" in the "StationName" column of the "Radio" table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources