mysql - SQL query BETWEEN incorrect result

Question

I have a database of startups, each startup has a total_funding field. I need to be able to search startup in the range of total_funding, for example, I am running those queries

 SELECT COUNT(*) AS total 
  FROM organization 
 WHERE 1 
   AND (organization.total_funding_usd BETWEEN 0 AND 1000000) 
 total : 931728

 SELECT COUNT(*) AS total 
  FROM organization 
 WHERE 1 
   AND (organization.total_funding_usd BETWEEN 1000000 AND 5000000) 
 total : 32415

SELECT COUNT(*) AS total 
  FROM organization 
 WHERE 1 
   AND (organization.total_funding_usd BETWEEN 0 AND 5000000) 
 total : 957721

The problem is if I add the results of the first two queries I am getting 931728 + 32415 = 964 143 and this is not equal to the result of the third query that is equal to 961 974

Does anyone have an idea why I am missing 2169 startups?

question from:https://stackoverflow.com/questions/65923955/sql-query-between-incorrect-result

Answer 1

Between includes the end points. I suspect you want:

WHERE organization.total_funding_usd >= 0 AND
      organization.total_funding_usd < 1000000

WHERE organization.total_funding_usd >= 1000000 AND
      organization.total_funding_usd < 5000000

WHERE organization.total_funding_usd >= 0 AND
      organization.total_funding_usd < 5000000

In other words, organizations with total funding of 1000000 are counted twice.