Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count/Sum and Grouping Query?

Status
Not open for further replies.
jenga

jenga

Technical User
Apr 29, 2005
1
US
I have the following query that I am trying to to run a COUNT/SUM and GROUP and just do not know enough to do it. I was hoping someone could help give me an idea on how to do it, syntax?

The query is:

SELECT
c.URL
, c.datetimecreated
, r.emailAddress
FROM Tracking c, recipients r
WHERE 1=1
AND c.recipientId = r.recipientId
AND c.broadcastId = <some id here>


The results set is:



ID DATETIMECREATED EMAILADDRESS URL
1 04-21-05 thisemail@email.com 2 04-21-05 thisemail@email.com 3 04-21-05 thisemail@email.com 4 04-21-05 thisemail@email.com 5 04-21-05 thisemail@email.com 6 04-21-05 anotheremail@email.com 7 04-21-05 anotheremail@email.com 8 04-21-05 anotheremail@email.com 9 04-21-05 anotheremail@email.com 10 04-21-05 anotheremail@email.com 11 04-21-05 anotheremail@email.com 12 04-21-05 anotheremail@email.com 13 04-21-05 anotheremail@email.com 14 04-21-05 anotheremail@email.com 15 04-21-05 thisemail@email.com 16 04-21-05 thisemail@email.com 17 04-21-05 thisemail@email.com 18 04-21-05 thisemail@email.com 19 04-21-05 thisemail@email.com 20 04-21-05 thisemail@email.com 21 04-21-05 thisemail@email.com 22 04-21-05 thisemail@email.com

Ideally, I would like to run the query where it would:

1) Group the URL and show total number of each URL. Similar to:
(Total 10)
(Total 21)
(Total 15)


2) Group each email and show the total clicks for that email address and the URLs clicked. For example:

thisemail@email.com (total URL clicks: 34)
URLs clicked:
anotheremail@email.com (total URL clicks: 42)
URLs clicked:
email@email.com (total URL clicks: 24)
URLs clicked:

3)Group the date and show the total number of clicks for that date and the URLs clicked

04-21-05 (Total Clicks 34)
URLs clicked:


Can anyone assist?
 
Sort by date Sort by votes
First, your resultset doesn't correspond to your query ...
1)
SELECT c.URL, COUNT(*) Total
FROM Tracking c, recipients r
WHERE c.recipientId = r.recipientId
AND c.broadcastId = <some id here>
GROUP BY c.URL
2) (partial)
SELECT r.emailAddress, COUNT(*) TotalClick, COUNT(DISTINCT c.URL) AS CountOfURL
FROM Tracking c, recipients r
WHERE c.recipientId = r.recipientId
AND c.broadcastId = <some id here>
GROUP BY r.emailAddress
3)
Left as an exercise (similar to 2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Count records until a record value changes
Replies
2
Views
356
carp
carp
sal21
  • Locked
  • Question
SELECT COUNT distinct
Replies
5
Views
213
sal21
sal21
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
186
carp
carp
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
201
carp
carp
jchewsmith
  • Locked
  • Question
Update Query with a MAX(Date) subquery 1
Replies
1
Views
248
JarlH
JarlH

Part and Inventory Search

Sponsor

Back
Top