sql - 一个查询中有两个 WHERE & COUNT

Question

我需要根据不同的条件在同一个查询中获得两个求和。两个条件都在同一列上运行。
有没有办法做到这一点？

我可以用一个例子来最好地解释这一点:

Table : salary_survey_result

Columns: industry, location, position, salary

实际上，我想结合以下两个查询:

SELECT industry, location, count(*) as MORE_THAN_SIX_FIGURE   
FROM salary_survey_result  
WHERE salary > 100000  
GROUP BY industry, location

和

SELECT industry, location, count(*) as MORE_THAN_FIVE_FIGURE  
FROM salary_survey_result  
WHERE salary > 10000  
GROUP BY industry, location

所以结果是这样的:
industry location MORE_THAN_FIVE_FIGURE MORE_THAN_SIX_FIGURE

Healthcare    NY               45                      10  
Healthcare    MN               35                       6   
InfoTech      NY               50                      19
InfoTech      MN               40                      12

Answer 1

就像是

SELECT industry, 
       location, 
       sum( case when salary >= 10000 then 1 else 0 end) as MORE_THAN_FIVE_FIGURE,
       sum( case when salary >= 100000 then 1 else 0 end) as MORE_THAN_SIX_FIGURE
  FROM salary_survey_result  
  WHERE salary >= 10000  
  GROUP BY industry, location

WHERE salary >= 10000结果不需要子句。如果 SALARY 上有索引，可能会提高性能并且大多数薪水值小于 10000。请注意，我还假设您的意思是 >=而不是 >因为 10,000 的薪水是五位数的薪水。