Trends in Startups question 2

How does SQL know to count the category companies from the table startups with the following code?

SELECT COUNT(*)
FROM startups;

why dont I have to dictate the title of a column?

SELECT COUNT(*) gives you the count of the NULL rows as well. I think if you select specific columns it will not count NULL rows. @lisalisaj may know best about this.

1 Like

SELECT COUNT(*) counts each row seperately…ie: all the rows including the NULL values.

you don’t have to specify the title of the column b/c it’s counting the rows. Isn’t each company a row in the table?

this is a helpful discussion about aggregate functions:
https://www.codecademy.com/learn/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-aggregates/cheatsheet

thanks- now i have another question for you…

so im looking at some of the hints on these questions and playing around with the code as opposed to just typing in the answer and it made me curious about this, how is the computer comprehending this?

SELECT location, AVG(employees)

FROM startups;

when i type the above two lines and run the code, it returns one location (san francisco) and 641 employees(which i assume is the average of all the companies. Why did it choose only San Francisco as the city to return?

It’s good that you’re poking around in the data & seeing what’s there and what works or doesn’t work.

You didn’t use GROUP BY on location

SELECT AVG(employees), location
FROM startups
GROUP BY location;

Which does what? results in the avg. no. of employees by each location.

I thought the question asked ’ What is the average size of a startup in each location , with average sizes above 500?’
This is where the difference between WHERE & HAVING applies…So, what’s the difference between those two?

i just dont understand why it returned san francisco, it seems like it was a random choice, though I doubt the computer would make a random choice and spit out one city randomly… im trying to find out why it chose san francisco as opposed to another city.

the 641 employees i can just assume is the average of all the start ups

I think it’s just selecting one row, but why SF(?).
and yes, you’re correct, 641 is the avg number of employees.
if you use GROUP BY it will give the avg. no. of employees in every location (row).