SQL: why it take so long for JOIN with this Query but, left JOIN has no problem?

here:

SELECT companies.name AS company_name,
       companies.status,
       COUNT(DISTINCT investments.investor_name) AS unqiue_investors
FROM tutorial.crunchbase_companies companies
JOIN tutorial.crunchbase_investments investments
  ON companies.permalink = investments.company_permalink
WHERE companies.state_code = 'NY'
GROUP BY 1,
         2
ORDER BY 3 DESC

Ques 2: and also, why we need to GROUP BY companies.status (mean '2' in the query), i thought companies.name is enough?

Thanks.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • In SQL, when doing an aggregation such as COUNT, SUM, MIN, or MAX, you need to group by all columns that are not affected by an aggregation function.  

    Like
Like Follow
  • 7 mths agoLast active
  • 1Replies
  • 48Views
  • 2 Following