Re: [QUESTION] SQL*Plus: Joins

In <325BAF33.67E7_at_e-mail.com> varad acharya <vacharya.ford_at_e-mail.com> writes:

>Frampton Steve R wrote:
>>
>> Hello:
>>
>> What is the proper way of doing something like this:
>>
>> select count(a.employee_id) male, count(b.employee_id) female
>> from employee_table a, employee_table b
>> where a.gender = 'M'
>> and b.gender = 'F';
>>
>> The statement as above return huge values that do not represent
>> actual counts. If the statement is broken up into two select
>> statements, the correct numbers are returned.
>>

>Try doing it this war
 

>select gender,count(*) from employee
>group by gender;

This will work, but in the original example both answers were returned in one row. To do that, try:

   select count(DECODE(gender, 'M', employee_id, NULL)) male,

          count(DECODE(gender, 'F', employee_id, NULL)) female    from employee_table;

-- 
------------------------------------------------------------------------------
Computer General		Rochester, NY			(716) 436-6372
      "... providing general computer solutions to specific business problems"
                              gwn_at_servtech.com