SQL [Oracle] comparing multiple rows

Question

I have a problem... I have table with informations about employees and I have to select sections, where is more employees without title then with ING title. How can i do that ? Why cant I use this mine SQL code ? It says single-row subquery returns more than one row. But i need to compare more than 1 row.

Thank for help :)

select section_number from employe where
 (select count(section_number) from employe where title='ING' group by section_number) 
> 
(select count(section_number) from employe where title IS NULL group by section_number) 
group by section_number;

is your group by column correct ???

psaraj12
– psaraj12

2015-03-22 03:58:28 +00:00
Commented Mar 22, 2015 at 3:58 — psaraj12
– psaraj12, Commented Mar 22, 2015 at 3:58

psaraj12 · Accepted Answer · 2015-03-22 04:05:00Z

3

You can use the below select

With ing_title as (select count(1) sec_ing,
section_number from employe where title='ING' 
group by section_number),

null_title as ( select count(1) sec_null,
section_number from employe where title is null group by section_number)

select ing_title.section_number 
from ing_title,null_title
where ing_title.sec_ing > null_title.sec_null
and ing_title.section_number=null_title.section_number

answered Mar 22, 2015 at 4:05

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mirek Mareš Over a year ago

Thank you :) I didnt even know that i can define 2 new tables from my table and then select that... :)

void · Accepted Answer · 2015-03-22 03:50:07Z

0

according to descriptions in the question, I think you need this:

select count(section_number),title 
from employe 
where title is null or title='ING' 
group by title 
Order by title nulls first

it will give you number of employees with no title and number of employees with title='ING'

answered Mar 22, 2015 at 3:50

void

7,9503 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

SQL [Oracle] comparing multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related