Sub Query Help with Count

Transact-SQL
1

Hi all, could someone help me write a subquery for these 2 queries, rather than having to create a a temp table? I've been trying to get it to work, but I'm not sure how to do it with the count.

Query 1

Select Date, Telephone, Count(*) as "Volume" 
Into #temp1
from gbr_calldetail
where datediff(month,calldatetime,getdate()) = 1 and CampaignWorkgroup = 'GBR ANI Purchase'
Group By Date, Telephone

Query 2

Select Date,Telephone, Volume
from #temp1 
Where Volume >= 3

Thanks in advance.

2

If you want to filter on aggregates (MIN, MAX, SUM etc) you should use HAVING instead of WHERE, you cannot use aliasses.

SELECT Date, Telephone, Count() as [Volume]
FROM gbr_calldetail
WHERE datediff(month,calldatetime,getdate()) = 1 and CampaignWorkgroup = 'GBR ANI Purchase'
GROUP BY Date, Telephone
HAVING Count() >=3

1 Like
3

And the reason you cannot use aliases with HAVING is explained here:

4

For best performance, you should avoid using functions against table columns; instead, do any calcs on static values, like this:


SELECT Date, Telephone, Count(*) as "Volume" 
FROM dbo.gbr_calldetail
WHERE calldatetime >= DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) - 1, 0) and --<<
    calldatetime < DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0) and --<<
    CampaignWorkgroup = 'GBR ANI Purchase'
GROUP BY Date, Telephone
HAVING COUNT(*) >= 3
1 Like
5

Thank you

6

Thanks Scott