CODEWITHSUNDEEP
sqloracle-database
Silent Storm
Silent Storm

Reputation: 21

Count number of employees that earn more than x value and less than x value by city

Table is named employee and columns are city, salary and employee

goal : in a single query , to count all the employees that earn >=1300 value less than <=1300 by city.

city stipendio employee
Milano 1200 employee1
Roma 1000 employee2
Milano 2000 employee3
Roma 900 employee4
Milano 1600 employee5

Query I did separately :

#1

select
CITTA,
COUNT(*)
FROM
employee
WHERE city in ( 'Milano', 'Roma')
and salary>=1300 
GROUP BY city;

#2

select
city,
COUNT(*)
FROM
employee
WHERE city in ( 'Milano', 'Roma')
and salary<=1300
GROUP BY city;

Upvotes: 0

Views: 853

Answers (2)

bhunter338
bhunter338

Reputation: 31

Try this.

select
city,
sum(case when salary<=1300 then 1 else 0 end) below,
sum(case when salary>1300 then 1 else 0 end) above


 from employee

WHERE city in ( 'Milano', 'Roma')
group by city

Upvotes: 1

MatBailie
MatBailie

Reputation: 86745

Use conditional aggregation. That is, include CASE expressions inside the aggregate.

SELECT
  city,
  SUM(CASE WHEN salary <= 1300 THEN 1 ELSE 0 END)  less_than_or_equal_to,
  SUM(CASE WHEN salary >  1300 THEN 1 ELSE 0 END)  greater_than
FROM
  employee
WHERE
  city IN ( 'Milano', 'Roma')
GROUP BY
  city;

Upvotes: 1

Related Questions