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
 How can I merge these two counting selects

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 NumOccurrences
FROM stats
GROUP BY eventtype
HAVING ( count(eventtype) > 0 )

AH found it
Go to Top of Page

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.Stats
WHERE EventType IN ('newsletter-clickthrough', 'newsletter-open')
AND ObjectID = @ID;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

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 CEMP
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -