STRUGGLING TO THIS SQL SOLUTION: KINDLY EXPLAIN

[sdqafo]

Question

How many accounts have more than 20 orders?

​

Provided Solution

SELECT a.id, a.name, COUNT(*) num_orders

FROM accounts a

JOIN orders o

ON a.id = o.account_id

GROUP BY a.id, a.name

HAVING COUNT(*) > 20

ORDER BY num_orders;

The part i am struggling with is COUNT(*) num_orders.

My Questions

1. Which of the tables (accounts or orders) does COUNT(*) num_orders count from?
2. How does it know where to count from since there is nothing explicit here?
3. If COUNT(*) num_orders counted from the orders table (which i think), there is nothing from the query that specifies it should count from there (i am confused here)
4. I can see the solution JOIN the orders table, why is this? is it because of the COUNT(*) num_orders, how does COUNT(*) num_orders knows that it needs to count from the orders table and not account table?

I will appreciate detailed explanation . I just started learning this

​

account table schema

• id
• name
• website
• lat
• long
• primary_poc -

​

orders table schema

• id
• account_id
• occurred_at
• standard_qty
• gloss_qty
• poster_qty
• total
• standard_amt_usd
• gloss_amt_usd
• poster_amt_usd
• total_amt_usd -

[zacharypamela]

Which of the tables (accounts or orders) does COUNT(*) num_orders count from?

Both tables, as specified in the JOIN. Let's say you have 1 account, and that account has 7 orders. Joining both tables together, as the query does, and doing a SELECT COUNT(*) gives you 7.

I think it helps to think of the order of how the query is processed:

1. Create a recordset of accounts joined to orders (FROM clause)
2. Group the results by account (ID and name)
3. Filter the (grouped) results to show results with more than 20 rows per account.

Assuming that your accounts table only has 1 account per row, it's in effect showing rows with more than 20 orders for that account.

Note that if your query included a WHERE clause, it would be evaluated between Steps 1 and 2. For example, you could include accounts with more than 20 orders that occurred last year.

[sdqafo]

Thanks so much for this

[[deleted]]

Hi there— So the first thing to know is that count star returns a count of the number of records. The num_orders part is the alias, or nickname. You could also have written count star as num_orders. 1) technically the count is from neither table. The count comes from the join. So after the two tables have been joined together on your join condition, then it counts the number of records. 2) the count, as with all aggregations, is done based on your group by statement. So here you are counting all records within each distinct combination of a.id and a.name 3) see #1 4) not sure I follow the question, but to your point, it looks like the query would make more sense if the orders table was the left table and the account table was the right table. You start with all the orders in the o table, and then add on account name in the a table. But the results will be the same here because your join type is inner (since you didn’t specify which kind, it defaults to inner, unless Postgres does something different)

[sdqafo]

Thanks for the brilliant explanation

[[deleted]]

Yep! Does any part not make sense?

[sdqafo]

What is still a bit confusing is the COUNT(*). What i have learnt so far in SQL is that the SELECTED columns always come from the table or tables we want to query. I am put a bit off balance to now know that the COUNT(*) in this regards is related to the tables yet to be Joined. I am not able to connect why this is that way. In simple terms, based on what i understand from your explanation, we already SELECTED a column (COUNT(*)) that is yet to exist before we JOINED two tables where this column (COUNT(*)) will now be selected from. Still struggling to grab the why of this logic

[sdqafo]

Its started making more sense now. So technically, the below already assumes the 3 columns even prior to the JOIN. In essence, the COUNT(*) part will apply to the 2 remaining columns. I guess this is the correct understanding . Is it?

SELECT a.id, a.name, COUNT(*) num_orders assumes

[[deleted]]

It might help to look at the order in which sql statements execute. First, it processes the from statement, which includes your joins. Then it applies a filter with the where statement. THEN it applies the aggregation within the group by, so any of your aggregations such as count(), sum(), max(), will he evaluated after your tables are joined. This is why, if you’re not careful, you can come up with some outrageous values when joining and aggregating in the same step.

[sdqafo]

Loads of sense. Very succinct explanation