Re: oracle sql query - output one line?

Ashish wrote:

> Try this:
>
> select
> count(empno),
> sum(decode(to_char(hiredate,'YYYY'),1980,1,0)) "1980",
> sum(decode(to_char(hiredate,'YYYY'),1981,1,0)) "1981",
> sum(decode(to_char(hiredate,'YYYY'),1982,1,0)) "1982",
> sum(decode(to_char(hiredate,'YYYY'),1987,1,0)) "1987"
> from emp
> /
>
> COUNT(EMPNO) 1980 1981 1982 1987
> ------------ --------- --------- --------- ---------
> 14 1 10 1 2
>
>
> captain_2010_at_yahoo.com wrote:
>

>> hi,
>>
>> Below is the output required, how ?
>>
>> Total    1980 1981  1982 1983
>> 20         1   3     5    2
>>
>>
>> SQL written below gives output as
>>
>>     TOTAL TO_C
>> --------- ----
>>         1 1980
>>        10 1981
>>         1 1982
>>         1 1987
>>
>> SQL:
>> select count(*) as Total,to_char(hiredate,'yyyy') from emp group by
>> to_char(hiredate,'yyyy');
>>
>> How to get output as given above i.e. in one line.
>>
>> Captain

Doing good math won't get you there. The data shows 10 in 1981 and you need to return 3. The data shows 1 in 1987 and you need to return 2 in a completely different year. That's why the demo makes no sense as provided.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)