Sumation of the sum columns

Hacker news SQL project, exercise 4 says:

Then, we want to add these users’ scores together and divide by the total to get the percentage.
their scores together and divide it by the total sum. Like so:

SELECT (1.0 + 2.0 + 3.0) / 6.0;

Now, I already have the code from the previous exercise 3:

SELECT user,  SUM(score)

FROM hacker_news 

GROUP BY user

HAVING score > 200 

ORDER BY 2 DESC;

So, what I am trying to avoid is to type the values (because if I had 300 values I would not type them) from the SUM(score) column and use a composition of functions to avoid that. I tried something like this but it did not work:

SELECT user,  SUM(SUM(score)) / COUNT(*)
FROM hacker_news 
GROUP BY user
HAVING score > 200 
ORDER BY 2 DESC;

Is there a way to avoid “manualy” looking at the value, then typing it, and dividing by the number of those values?

It’s always worth linking the lesson with a URL whenever possible, I vaguely recognise it as I had the same question when I reached it but it’s likely viewers may have no idea, even if they’ve done the course before.

There are ways to do this (temporary tables, joins, subqueries and window functions etc.) but I don’t know if they’re introduced until later in the course (depending on the course you’re on, some of these may be in separate sql lessons; joins at least are probably in your course). It’s probably worth waiting until these are introduced before trying it for yourself. If you’re very curious you can hunt down some of this information now but it may take more effort.

I think this is the right link, feel free to correct if not…
https://www.codecademy.com/courses/learn-sql/projects/sql-hackernews

1 Like

Thanks for the reply. I passed through the basic SQL course on Codecademy, I am now practicing just the projects again. There have been no subqueries (wondering in which course are they) but I will see later how it can be done.

1 Like

Multiple tables (including a brief intro to CTEs / WITH) follow this project. There are no lessons on windows functions or much depth into subqueries here. That content can be found in the Analyze Data with SQL course or on the DS Path.

1 Like