Help with subquery/join/group by/having

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

Help with subquery/join/group by/having

Author

Topic

Stanham
Starting Member

1 Post

Posted - 2014-02-08 : 21:20:55

I'm trying to write a subquery for an e-mail marketing project, with two tables (and the columns I need) below:

Table: Email_Send:
Column: E-mail_ID
Column: Subject_Line
Column: Send_Date

Table: Email_Event:
Column: Email_ID
Column: Recipient_ID
Column: Recipient_Name
Column: Event_Type

I'm trying to return Recipient_Name, Count of the Event_Types = "Click", Subject_Line, and Send_Date.

I've gotten the inner query to work perfectly:

SELECT Email_Event.Recipient_ID,
Email_Send.Email_ID,
COUNT(Email_Event.Event_Type)
FROM Email_Send
INNER JOIN Email_Event ON Email_Send.Email_ID = Email_Event.EmailID
WHERE (Email_Event.Event_Type = 'Click')
AND (Email_Send.Send_Date > GETDATE() - 3)
GROUP BY Email_Event.Recipient_ID, Email_Send.Email_ID
HAVING (COUNT(Email_Event.Event_Type) > 4))

This returns three columns, the Recipient_ID, Email_ID, and Count of Event_Types. I have to use Recipient_ID and Email_ID as opposed to Recipient_Name and Email_Line in case duplicates.

I've tried multiple subqueries, but how can I add Recipient_Name, Subject_Line, and Send_Date to get returned along with Count(Event_Type)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-10 : 08:37:44

[code]
SELECT *
FROM
(
SELECT Email_Event.Recipient_Name,
Email_Send.Subject_Line,
COUNT(Email_Event.Event_Type) OVER (PARTITION BY Email_Event.Recipient_ID, Email_Send.Email_ID) AS EventTypeCnt
FROM Email_Send
INNER JOIN Email_Event ON Email_Send.Email_ID = Email_Event.EmailID
WHERE (Email_Event.Event_Type = 'Click')
AND (Email_Send.Send_Date > GETDATE() - 3)
)t
WHERE EventTypeCnt > 4[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources