-
Using views with joins
I have created 2 views for the same table and I was wonder if it was possible to use a join across these 2 views:
View 1:
CREATE VIEW Hits_per_day
AS
select CONVERT(char(12), TIMESTAMP, 2) AS Day, usergroup, count(*) as Hits
from newslog
where newsevent = "APPSTART"
group by CONVERT(char(12), TIMESTAMP, 2), usergroup
View 2:
CREATE VIEW daily_hits AS
select CONVERT(char(12), TIMESTAMP, 2) AS Day, count(*) as DailyHits
from newslog
where newsevent = "APPSTART"
group by CONVERT(char(12), TIMESTAMP, 2)
I would like to use these 2 views to produce the following result set or close enough:
Date usergroup hits DailyHits
07/02/00 DPRS 10 10
08/02/00 DPRS 12 12
09/02/00 DPRS 12
09/02/00 Members 1 13
10/02/00 DPRS 15
10/02/00 Members 1 16
11/02/00 DPRS 9 9
12/02/00 DPRS 2 2
13/02/00 DPRS 4
13/02/00 Members 1
13/02/00 Senators 2 7
I can use a query against view 1 to sum the dailyhits but this is recorded on another row:
select "Date" = (Substring(day,7,2) + '/' + SUBSTRING(day,4,2) + '/' + SUBSTRING(day,1,2)) , usergroup, hits from hits_per_day order by Substring(day,4,2), SUBSTRING(day,7,2) compute sum(hits) by Substring(day,4,2), SUBSTRING(day,7,2)
Results:
Date usergroup hits
07/02/00 DPRS 10
sum
===========
10
Date usergroup hits
08/02/00 DPRS 12
sum
===========
12
Date usergroup hits
09/02/00 DPRS 12
09/02/00 Members 1
sum
===========
13
Is there a nicer way to display the results with the total of hits for each day on the same line as the other details.
Thank you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|