Syntax Question

Hi Guys,

I had an exercise where I needed to count the average score for specific hours (example at 14:00 there was an average score per post of 40 and at 15:00 it was 30, etc.). Then, in addition, I had to count the number of posts made in that hour.

I had put in the code:

SELECT strftime('%H', timestamp) AS time, AVG(score), COUNT(titles)
FROM hacker_news
GROUP BY time;

This answer wasn’t correct and I had to change the
COUNT(titles) to COUNT(*)

Does anyone know why I wasn’t able to execute the code with COUNT(titles) ?

Correct Code

SELECT strftime('%H', timestamp), 
   AVG(score),
   COUNT(*)
FROM hacker_news
GROUP BY 1
ORDER BY 1;
1 Like

This is because titles in your case contains probably a string. it also only contains one item so there is nothing to count.

With COUNT(*) you count all the data rows that come in trough your select statement.
Its kinda the same as saying:

SELECT strftime('%H', timestamp) AS time, AVG(score), 
COUNT(SELECT titles FROM hacker_news)  // <--- note the subquery
FROM hacker_news

Hope this clarify’s things.

2 Likes