SELECT QUERY WITH COUNT()

Question

In this query there is an error:

"Column 'upload_news.upload_time' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

I want to display number of records that are related to distinct country with country name between two dates;

select count(Distinct news_id) AS TotalRecords, country 
from upload_news 
group by country 
having [upload_time] between GetDate()-2 AND GetDate()

Answer 1

The HAVING clause will run after all the data is grouped, thus you can only filter on aggregations of grouped data.

If you want to filter out rows before the data is grouped, you need to use the WHERE clause:

select count(Distinct news_id) AS TotalRecords, country 
from upload_news 
where [upload_time] between GetDate()-2 AND GetDate()
group by country 

Note, the WHERE clause needs to go before the GROUP BY clause.

Answer 2

This query:

select count(Distinct news_id) AS TotalRecords, country 
 from upload_news 
group by country 
having [upload_time] between GetDate()-2 AND GetDate()

Should be:

 select count(Distinct news_id) AS TotalRecords, country 
 from upload_news 
 where upload_time between (NOW()- INTERVAL 2 DAY) AND NOW()
 group by country