GROUP BY, COUNT(*) and other such problems

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

SQL Server Development (2000)

GROUP BY, COUNT(*) and other such problems

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-01-31 : 09:07:26

Hi folks

I'm currently developing a statistics report generation system, that has it's front-end in ASP.NET which then queries a MS SQL Server 2000 database.

Here is an example of a stored procedure used to work out the total number of visitors per day, over a given date period.


CREATE PROCEDURE sppb_GandalfStats_TotalVisitors

@DateFrom		DATETIME,
@DateTo		DATETIME,
@NoDateSpan		BIT

/****************************************************************************************
31 January 2005 - Created - Peter Bridger
Get website visitor stats for a time period
***************************************************************************************/

AS

SET NOCOUNT ON

IF @NoDateSpan = 1 BEGIN
	SELECT @DateFrom = '2000/01/01'
	SELECT @DateTo = '2100/01/01'
END

SELECT 
COUNT(*) 'TotalVisitors',
CONVERT( DATETIME, CONVERT( CHAR(10), StartDatetime, 101 ) ) 'Date'
FROM
Visitors
WHERE
StartDatetime >= @DateFrom AND 
StartDatetime <= DATEADD( dd, 1, @DateTo )
GROUP BY
CONVERT( DATETIME, CONVERT( CHAR(10), StartDatetime, 101 ) )
ORDER BY
CONVERT( DATETIME, CONVERT( CHAR(10), StartDatetime, 101 ) ) ASC

/* EOF */
GO

This works fine, although if there's a better way to do this, please let me know

However I also need to write a procedure to work out how many people are using which type of browsers for a given time period.
Here is the code I thought would work, but doesn't.


SELECT 
COUNT(*),
L_BT.ShortName
FROM
Visitors AS V
LEFT JOIN
Lookup_BrowserTypeID AS L_BT ON
L_BT.BrowserTypeID = V.BrowserTypeID
WHERE
V.StartDatetime >= @DateFrom AND 
V.StartDatetime <= DATEADD( dd, 1, @DateTo )
GROUP BY
COUNT(*),
L_BT.ShortName

The problem being, it doesn't like the COUNT(*) statements used.
All I'm after is a list of browsers and the number of times they were logged for the date period requested.

E.g.
MSIE, 544
Mozilla, 232

Thanks if you can help me out

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-01-31 : 09:36:56

In your second query you need to group on a column and not on COUNT(*) if you replace the line GROUP BY COUNT(*) with GROUP BY L_BT.ShortName it should work.

Dustin Michaels

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-01-31 : 09:44:19

*slaps head*

Of course, I should never try and write stored procedures on a Monday. Thanks!

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-01-31 : 10:47:03

This maybe just 'Monday syndrome' again, but I've hit another stumbling block.

I now want to query the database and work out the top 10 most popular 'entry' pages to our website. So bascially, the first page a person hits when visiting the website.

This needs to be generated from two tables


CREATE TABLE [dbo].[Visitors] (
	[VisitorID] [int] IDENTITY (1, 1) NOT NULL ,
	[StartDatetime] [smalldatetime] NOT NULL ,
	[EndDatetime] [smalldatetime] NULL ,
	[BrowserTypeID] [tinyint] NOT NULL ,
	[BrowserMajorVersion] [tinyint] NOT NULL ,
	[BrowserMinorVersion] [tinyint] NOT NULL 
)

The first time a user visits the site a row is generated in the Visitors table. The VisitorID generated is then held, to track this visitor.


CREATE TABLE [dbo].[PageViews] (
	[PageViewID] [int] IDENTITY (1, 1) NOT NULL ,
	[PageID] [int] NOT NULL ,
	[ViewDatetime] [smalldatetime] NOT NULL ,
	[VisitorID] [int] NULL 
)

Every page visited is logged to this table, along with the VisitorID of the personing viewing it.

From this data, the PageView row with the lowest ViewDatetime is the entry page for the Visitor row is joins with.
The trouble is I'm having problems writing the SQL to determine this, help again please!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources