0

Hi all i'm just wondering if anyone could suggest the easiest way to write this query. The problem with writing the subquery HomePageHits this way is that is bring the same records for the Hits and the HomePageHits columns. Action is a column on the mytable table.

SELECT 
,KEYID
,DATE
,COUNT(*) Hits
,(select COUNT(*) from mytable where Action = 'Home') as HomePageHits
from mytable where column = 'Home'
where date = getdate()-5 and keyid = 12345
2
DECLARE @d DATE = DATEADD(DAY, -5, CURRENT_TIMESTAMP);

SELECT 
  keyid = 12345,
  [date] = d,
  Hits = COUNT(*),
  HomePageHits = COUNT(CASE WHEN Action = 'Home' THEN 1 END)
FROM dbo.mytable
WHERE [date] >= @d
  AND [date] < DATEADD(DAY, 1, @d)
  AND keyid = 12345;

(I'm assuming where column='Home' was a typo.)

Some tips:

Not the answer you're looking for? Browse other questions tagged or ask your own question.