Re: Need help with a SQL query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 28 Mar 2003 15:36:36 GMT
Message-ID: <b61q63$tb4u$1_at_ID-82536.news.dfncis.de>

> Yes, you are correct. A simple union will perform the extra step of removing
> duplicates. When your query will obviously not have duplicates the union all
> is a more efficient choice.
>
> Joe

Speaking about efficency.... It's not a big job for oracle to determine if there are duplicates in three rows. This is almost a no-op. On the other side, three union (all)'s incure three full table scans, which might be heavy load, depending of the size of the underlying tables.

So, I'd suggest that one tries to have only on FTS:   

    select

        case 
            when r=1 then 'a' 
            when r=2 then 'b' 
            when r=3 then 'c' 
        end attribute,
        case 
            when r=1 then cnt_a
            when r=2 then cnt_b
            when r=3 then cnt_c
        end attribute
    from 
          (select count(a) cnt_a,count(b) cnt_b,count(c) cnt_c from table1) cnt,
          (select rownum r from user_objects where rownum <= 3);

hth
Rene Nyffenegger

>
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:3Fsga.1837$ec3.1296_at_news01.roc.ny.frontiernet.net...

>> Not wanting to nitpick .. and it does not matter much in this case.
>> But imho using "union all" is better when you don't really need union.
>>
>> Anurag
>>
>> "Joseph Wilson" <me_at_home.now> wrote in message

 news:qWrga.2735$2x2.1154317_at_dca1-nnrp1.news.algx.net...

>> > select 'a' as ATTRIBUT , count(*) as no from table1 where a is not null
>> > UNION
>> > select 'b' as ATTRIBUT, count(*) as no from table1 where b is not null
>> > UNION
>> > select 'c' as ATTRIBUT, count(*) as no from table1 where c is not null
>> >
>> >
>> > "Anders Windelhed" <anders_at_underlandstudios.com> wrote in message
>> > news:txqga.30193$oe.93724_at_amstwist00...
[Quoted] >> > > I need help with a SQL query...
>> > >
>> > > I have three queries:
>> > >
>> > > (select 'a' as ATTRIBUT , count(*) as no from table1 where a is not

 null)
>> > >
>> > > (select 'b' as ATTRIBUT, count(*) as no from table1 where b is not  null)
>> > >
>> > > (select 'c' as ATTRIBUT, count(*) as no from table1 where c is not  null)

>> > >
>> > > the results from these queries looks like this
>> > >
>> > > attribut no
>> > > a   34
>> > >
>> > > and so on...
>> > >
>> > > I would like to combine into one result table like this
>> > >
>> > > attribut   no
>> > > a           34
>> > > b          334
>> > > c           23
>> > >
>> > > I know that it would be much easier to create a query that gives the

 result

[Quoted] >> > > like this
>> > >
>> > > a      b      c
>> > > 34   334  23
>> > >
>> > > but that won't solve my problem =(
>> > >
>> > > Any suggestions?
>> > >
>> > > Best regards
>> > >
>> > > Anders
>> > >
>> > >
>> >
>> >
>>
>>

>
>

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Fri Mar 28 2003 - 16:36:36 CET