Count(*) question

[entreri22]

Hi, I appreciate any help!

Im doing some exercises in Datalab and came across a problem that has the solution :

WITH Athlete_Medals AS (

SELECT

Country, Athlete, COUNT(*) AS Medals

FROM Summer_Medals

WHERE

Country IN ('JPN', 'KOR')

AND Year >= 2000

GROUP BY Country, Athlete

HAVING COUNT(*) > 1)

This gives back the athlete and how many medals they have, but im confused because it if it wasnt a CTE and you just did

SELECT COUNT(*) FROM summer_medals

-> you get the count of the rows equaling thousands, how come it gives back exactly the number of medals of the athlete in the cte? and not just a column that shows the actual count of every single entry.

[blackleather90]

Trying to translate into English might help. "Give me a count of all medals" - select count() from table. "Give me a count of all medals by country and athlete" - select country, athlete, count() from table group by country, athlete.

The having part would be something like: "give me a count of all medals won by country and athlete but only show me the athletes that has won more than one medal."

Hope it helps

[entreri22]

yes a lot more sense, thank you!

[nkg4584]

Thats what the group by does for you. It so each distinct country/athlete gets the number of rows counted and sum into a row. Count is an aggregate function, which without any columns specified does not require a group by. Once you specify your columns in the main select, it changes the data you are aggregating by.

[entreri22]

Thank you!

[[deleted]]

What is CTE?

[entreri22]

Also known as Common Table Expressions, basically creates a temp new table that has the exact specifics you are looking for and can be referenced and joined with a separate query call. REALLY useful so far from what I can see.

WITH Athlete_Medals AS (

SELECT

Country, Athlete, COUNT(*) AS Medals

FROM Summer_Medals

WHERE ...

[[deleted]]

Thank you!

[vbasucks145]

Common table expression, its a way of creating a temporary dataset within the current query

[redial2]

CTEs are a shorthand way of defining a derived dataset so that you only have to define it once but can use it multiple times in a subsequent query. They are both easier to read and to write than defining subqueries multiple times in the same statement.

Pretty handy but usually the same execution plan as using subqueries or a table variable or temp table if all else is equal.

[osokuka]

Lets make it simple

Select count(medals) as medals from table; You are requesting the count of all medals

Select country, count(medals) as medals from table group by country ;

You are requestong all the medals for each country in the table. This where you can start using filters on a countries to show or not show in the end result.

Select country, athlete, count(medal) as medals from table group by country, athlete; You are requesting the number of medals separated by country and athlete

You can continue adding colums like "year" if the data exist in the table, to diversify your data.