In the free SQL course, there is the last project called hacker news. Task 5 says:
Oh no! While we are looking at the power users, some users are rickrolling — tricking readers into clicking on a link to a funny video and claiming that it links to information about coding.
url of the video is:
How many times has each offending user posted this link?
My solution is:
SELECT user, COUNT("https://www.youtube.com/watch?v=dQw4w9WgXcQ") FROM hacker_news GROUP BY user ORDER BY 2 DESC;
Why is my solution incorrect?
COUNT cannot count a URL string.
You’re supposed to count how many times the “offending user” has posted that link.
What is another way one could write this query? What clause would filter rows where you could search for something based on a condition
LIKE that particular url?
My solution is this:
SELECT user, COUNT(*)
WHERE url = 'https://www.youtube.com/watch?v=dQw4w9WgXcQ'
GROUP BY user
ORDER by 2 DESC;
Is there a reason why COUNT cannot count strings? I found it on this post on stackoverflow:
[sql server - (sql) how can I use count() method when data type is text? - Stack Overflow](https://it says here that COUNT cannot be used to count the string data type)
You have to
COUNT(*) the rows that meet a particular condition.
You have to use
Be careful when hunting SQL information, there are various implementations that unfortunately often have different behaviour. That link is to an SQL server question and mentions the
TEXT data type which seems to have been deprecated anyway for
VARCHAR (which apparently can be counted). You can count text columns in sqlite which is what the lessons normally use unless otherwise specified.