Grouping Problem - 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

Grouping Problem

Author

Topic

sqlns
Starting Member

3 Posts

Posted - 2009-11-17 : 15:40:23

Hi, I am relatively new to SQL and very new to SQL Server. I am trying to write a query (below), that will display a list of page urls, date accessed and the number of people that have accessed the page each day. I know my query here is wrong but I can't for the life of me work it out.

Current output (using this query) is:
02 Jun 2009 resume.aspx 1
03 Jan 2009 contact.aspx 1
04 Mar 2009 skills.aspx 1
16 Jul 2009 about.aspx 2
16 Nov 2009 about.aspx 1
16 May 2009 contact.aspx 1
16 Aug 2009 contact.aspx 1
16 Oct 2009 contact.aspx 1
16 Aug 2009 resume.aspx 1
16 Nov 2009 resume.aspx 1
16 Nov 2009 resume.aspx 1
16 Nov 2009 resume.aspx 1
16 Sep 2009 skills.aspx 1

As you can see, the pages aren't being grouped by day. I'm not sure whether this is enough info but can anyone please help??

SELECT
CONVERT(CHAR(11), accessedDate, 106) as Date,
pageName,
COUNT(accessedDate) as TimesAccessed
FROM pageAccessed LEFT JOIN page ON page.pageID = pageAccessed.pageID GROUP BY DATEPART(dd, accessedDate), pageName, accessedDate

Table 1:
Table Name: page
pageID pageName pageContent
1 resume.aspx Resume page content
2 skills.aspx Skills page content
3 contact.aspx Contact page content
4 about.aspx About page content

Table 2 Info:
Table Name: pageAccessed
accessedID pageID accessDate
1 1 16/11/2009 20:35
2 4 16/11/2009 20:36
3 1 02/06/2009 20:38
4 3 16/05/2009 20:38
5 3 03/01/2009 20:38
6 2 04/03/2009 20:38
7 3 16/08/2009 20:38
8 1 16/11/2009 20:38
10 4 16/07/2009 20:39
11 4 16/07/2009 20:39
12 1 16/08/2009 20:39
13 2 16/09/2009 20:39
14 3 16/10/2009 20:39
15 1 16/11/2009 20:39

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 15:45:32

Help us a little more...

What do you want the final result to look like?

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

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-11-17 : 15:46:15

SELECT
CONVERT(CHAR(11), accessedDate, 106) as Date,
pageName,
COUNT(accessedDate) as TimesAccessed
FROM pageAccessed LEFT JOIN page ON page.pageID = pageAccessed.pageID GROUP BY convert(char(11), accessedDate, 106), pageName

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

sqlns
Starting Member

3 Posts

Posted - 2009-11-17 : 16:26:49

Super fast! Thank you!!!

Can you help with another? I'm having a similar (but different) problem:

For this one, I'm trying to display a list of all contact email addresses, the number of times that have tried to email and how many failed(0) or succeeded(1).

Current output (using this query) is:
company1@owner.com 2 1 1
company1@owner.com 1 1 0
company1@owner.com 3 1 2
company2@owner.com 4 1 3
company2@owner.com 1 0 1
company3@owner.com 1 1 0

As yo can see, I want similar email addresses grouped, but they aren't. (I know this isn't great table design but the point here is that I figure out the grouping functions).

SELECT e.emailAddress,
count(*) AS [Total Attempts],
(SELECT COUNT(sent) FROM emailAttempt ea1 WHERE sent = 1 AND ea1.emailID = ea.emailID ) AS Succeeded,
(SELECT COUNT(sent) FROM emailAttempt ea2 WHERE sent = 0 AND ea2.emailID = ea.emailID ) AS Failed
FROM emailAttempt ea LEFT JOIN email e
ON e.emailID = ea.emailID
GROUP BY e.emailAddress, ea.emailID
ORDER BY e.emailAddress

Table 1:email
emailID emailFName emailLName emailAddress
1 Company1 LName1 company1@owner.com
2 Company2 LName2 company2@owner.com
3 Company3 LName3 company3@owner.com
4 Company1 LName1 company1@owner.com
5 Company1 LName1 company1@owner.com
6 Company6 LName6 company6@owner.com
7 Company3 LName3 company3@owner.com
8 Company1 LName1 company1@owner.com
12 Company2 LName2 company2@owner.com

Table 2:emailAttempt
attemptID emailID sent
1 1 0
2 1 1
3 2 0
4 2 0
5 2 0
6 2 1
7 3 1
8 4 1
9 5 0
10 5 0
11 5 1
12 12 0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-11-18 : 08:50:39

SELECT
e.emailAddress,
count(*) AS [Total Attempts],
sum(sent) as Succeeded,
sum(
case(sent)
when 1 then 0
else 1
end
)AS Failed
FROM emailAttempt ea LEFT JOIN email e
ON e.emailID = ea.emailID
GROUP BY e.emailAddress
ORDER BY e.emailAddress

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

sqlns
Starting Member

3 Posts

Posted - 2009-11-18 : 15:07:15

THANK YOU jhocutt!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources