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.
Author |
Topic |
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2014-03-21 : 09:26:22
|
I have two select statements: SELECT COUNT(id) as 'clickthrough' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-clickthrough'SELECT COUNT(id) as 'open' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-open'They are identical except counting different eventtypes. There must be a better way to do this, so I can get both bits of data in one query. Any ideas, many thanks in advance? |
|
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2014-03-21 : 09:31:17
|
SELECT eventtype, COUNT(eventtype) AS NumOccurrencesFROM statsGROUP BY eventtypeHAVING ( count(eventtype) > 0 )AH found it |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-03-21 : 10:53:25
|
[code]SELECT SUM(CASE WHEN EventType = 'newsletter-clickthrough' THEN 1 ELSE 0 END) AS ClicktTrough, SUM(CASE WHEN EventType = 'newsletter-open' THEN 1 ELSE 0 END) AS [Open]FROM dbo.StatsWHERE EventType IN ('newsletter-clickthrough', 'newsletter-open') AND ObjectID = @ID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
MuralikrishnaVeera
Posting Yak Master
129 Posts |
Posted - 2014-03-22 : 01:03:03
|
Hope this will help you............CREATE TABLE CountingEmp(ID INT IDENTITY,ObjecvtID INT ,EventType VARCHAR(100) )INSERT INTO CountingEmp VALUES(1,'newsletter-clickthrough'), (2,'newsletter-open'),(1,'newsletter-clickthrough'),(2,'newsletter-open'),(1,'newsletter-clickthrough'),(2,'newsletter-open')SELECT DISTINCT(SELECT COUNT(C.ID) FROM CountingEmp as c WHERE c.EventType = 'newsletter-clickthrough') AS Clickthrough , (SELECT COUNT(cm.ID) FROM CountingEmp As cm WHERE cm.EventType = 'newsletter-open') AS Opens FROM CountingEmp AS CEMPMurali KrishnaYou live only once ..If you do it right once is enough....... |
|
|
|
|
|
|
|