Subquery for totals - 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

Subquery for totals

Author

Topic

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-07-17 : 11:13:50

Hi,

I use the following query in my stats page to find the unique visitors and the pages they visited per day in a given month.

SELECT
CONVERT(CHAR(10),LogDate,103) As Date_,
Count(DISTINCT LogRemote_Addr) As Visitors,
Count(Lid) As Pages
FROM Log
WHERE LogMonth=7
Group by CONVERT(CHAR(10),LogDate,103)
ORDER BY CONVERT(CHAR(10),LogDate,103)

I would like to have the totals (Sum) of the "Visitors" and "Pages" also for the given month.
I think I have to use a subquery to accomplish that but I can't figure it out. I would appreciate your help.
Thanks,

Deni

www.tabletennis.gr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 11:37:08

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, LogDate), 0) As Date_,
Count(DISTINCT LogRemote_Addr) As UniqueVisitors,
Count(LogRemote_Addr) As TotalVisitors,
Count(DISTINCT Lid) As UniquePages,
Count(Lid) As TotalPages
FROM Log
WHERE LogDate >= '20070101'
AND LogDate < '20070801'
Group by DATEADD(DAY, DATEDIFF(DAY, 0, LogDate), 0)
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, LogDate), 0)[/code]
Peter Larsson
Helsingborg, Sweden

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-07-17 : 11:50:45

It doesn't work. I don't take the correct results.

www.tabletennis.gr

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-17 : 11:57:13

It would help if you posted some DDL, sample data and your expected results then

Read the hint link in my sig and post what it asks for

You should get an answer in minutes after your post

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-07-17 : 12:10:39

Ok. I have the following fileds.
LogDate = date of visit
LogRemoteAddr = ip of the visitor
Lid = a common id (autonumber)

The example

Lid LogDate LogRemoteAdd
1 15/7/2007 85.82.141.1
2 15/7/2007 85.82.141.1
3 15/7/2007 85.21.140.58
4 16/7/2007 85.21.140.58
5 16/7/2007 193.54.147.1
6 17/7/2007 193.57.100.1

This set would produce

Date Visitors Pages
15/7/2007 2 3
16/7/2007 2 2
17/7/2007 1 1

I would like to take the Sum of Visitors and Pages which is to 5 and 6 correlatively.

I think it's more clear now.
Thanks

www.tabletennis.gr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 12:21:44

Where would you like to take them? Picnic?

Peter Larsson
Helsingborg, Sweden

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-07-17 : 12:47:46

Not PicNic.

The example was clear. Can you help?

www.tabletennis.gr

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 12:52:27

[code]
SET DATEFORMAT dmy
go

Declare @logvisits table (Lid int, LogDate datetime, LogRemoteAdd varchar(50))
insert into @logvisits
select 1, '15/7/2007', '85.82.141.1' union all
select 2, '15/7/2007', '85.82.141.1' union all
select 3, '15/7/2007', '85.21.140.58' union all
select 4, '16/7/2007', '85.21.140.58' union all
select 5, '16/7/2007', '193.54.147.1' union all
select 6, '17/7/2007', '193.57.100.1'

select * from @logvisits

Select LPages.logDate, Visitors=Lvisit.vcount, Pages=Lpages.Pages
From (
Select LogDate, count(*) as Pages
From @logvisits
Group by LogDate
) LPages
Join (
Select l3.LogDate , count(*) as Vcount from (
Select LogDate, LogRemoteAdd, count(*) as Visitors
from @logvisits
Group by LogDate, LogRemoteAdd
) l3 group by l3.logdate
) LVisit on LPages.LogDate = Lvisit.logdate

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-07-18 : 05:47:46

Maybe the answer is a creation of a temporary table with the fields "Visitors", "Pages" and then call it from a simple Select statement with SUM.
Any help??

www.tabletennis.gr

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 12:16:54

Did you try the query I posted? Was it not helpful?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources