Hi all,

I have a query as follows:

Select building, floor from T1

I would like to limit the output where the building, floor combination
is present only once in the output.

For example, if the output is

blgdA, 2
bldgA, 3
bldgA, 3
bldgB, 1
bldgB, 2
bldgB, 2

I would like to filter it so that the output is only
blgdA, 2
bldgB, 1

I'm not sure about how to do this.

Can someone help out?
Thanks,
Sashi

Sashi schrieb:
> Hi all,
>
> I have a query as follows:
>
> Select building, floor from T1
>
> I would like to limit the output where the building, floor combination
> is present only once in the output.
>
> For example, if the output is
>
> blgdA, 2
> bldgA, 3
> bldgA, 3
> bldgB, 1
> bldgB, 2
> bldgB, 2
>
> I would like to filter it so that the output is only
> blgdA, 2
> bldgB, 1
>
> I'm not sure about how to do this.
>
> Can someone help out?
> Thanks,
> Sashi
Search the docs for GROUP BY and HAVING.

htht,
Urs Metzger

On May 12, 10:55 am, Urs Metzger <u...@ursmetzger.de> wrote:
> Sashi schrieb:
>
> > Hi all,
>
> > I have a query as follows:
>
> > Select building, floor from T1
>
> > I would like to limit the output where the building, floor combination
> > is present only once in the output.
>
> > For example, if the output is
>
> > blgdA, 2
> > bldgA, 3
> > bldgA, 3
> > bldgB, 1
> > bldgB, 2
> > bldgB, 2
>
> > I would like to filter it so that the output is only
> > blgdA, 2
> > bldgB, 1
>
> > I'm not sure about how to do this.
>
> > Can someone help out?
> > Thanks,
> > Sashi
>
> Search the docs for GROUP BY and HAVING.
>
> htht,
> Urs Metzger

Thank you.
So I modified the query thus:
select bldg, count(Floor)
from T
group by bldg
having count(Floor) =1

However, when I try to include the floor number
select lbcode_key, count(floor ), floor
it fails: ORA-00979: not a GROUP BY expression 
I want to be able to see the floor number.
Also there is another field "type" and I need to filter it by this
field as well, to include the regexp '%Switch'.

So my original query should have been:
Select building, floor from T1 where type like '%Switch'

However, the where clause is disallowed when using group by and the
having clause doesn't like this either.

How do I do this?

Sashi schrieb:
> On May 12, 10:55 am, Urs Metzger <u...@ursmetzger.de> wrote:
>> Sashi schrieb:
>>
>>> Hi all,
>>> I have a query as follows:
>>> Select building, floor from T1
>>> I would like to limit the output where the building, floor combination
>>> is present only once in the output.
>>> For example, if the output is
>>> blgdA, 2
>>> bldgA, 3
>>> bldgA, 3
>>> bldgB, 1
>>> bldgB, 2
>>> bldgB, 2
>>> I would like to filter it so that the output is only
>>> blgdA, 2
>>> bldgB, 1
>>> I'm not sure about how to do this.
>>> Can someone help out?
>>> Thanks,
>>> Sashi
>> Search the docs for GROUP BY and HAVING.
>>
>> htht,
>> Urs Metzger
>
> Thank you.
> So I modified the query thus:
> select bldg, count(Floor)
> from T
> group by bldg
> having count(Floor) =1
>
> However, when I try to include the floor number
> select lbcode_key, count(floor ), floor
> it fails: ORA-00979: not a GROUP BY expression 
> I want to be able to see the floor number.
> Also there is another field "type" and I need to filter it by this
> field as well, to include the regexp '%Switch'.
>
> So my original query should have been:
> Select building, floor from T1 where type like '%Switch'
>
> However, the where clause is disallowed when using group by and the
> having clause doesn't like this either.
>
> How do I do this?
OK:

select bldg, floor
from T
group by bldg, floor
having count(*) = 1

hth,
Urs Metzger

Sashi schrieb:
> On May 12, 10:55 am, Urs Metzger <u...@ursmetzger.de> wrote:
>> Sashi schrieb:
>>
>>> Hi all,
>>> I have a query as follows:
>>> Select building, floor from T1
>>> I would like to limit the output where the building, floor combination
>>> is present only once in the output.
>>> For example, if the output is
>>> blgdA, 2
>>> bldgA, 3
>>> bldgA, 3
>>> bldgB, 1
>>> bldgB, 2
>>> bldgB, 2
>>> I would like to filter it so that the output is only
>>> blgdA, 2
>>> bldgB, 1
>>> I'm not sure about how to do this.
>>> Can someone help out?
>>> Thanks,
>>> Sashi
>> Search the docs for GROUP BY and HAVING.
>>
>> htht,
>> Urs Metzger
>
> Thank you.
> So I modified the query thus:
> select bldg, count(Floor)
> from T
> group by bldg
> having count(Floor) =1
>
> However, when I try to include the floor number
> select lbcode_key, count(floor ), floor
> it fails: ORA-00979: not a GROUP BY expression 
> I want to be able to see the floor number.
> Also there is another field "type" and I need to filter it by this
> field as well, to include the regexp '%Switch'.
>
> So my original query should have been:
> Select building, floor from T1 where type like '%Switch'
>
> However, the where clause is disallowed when using group by and the
> having clause doesn't like this either.
>
> How do I do this?
You need to group by (at least) all columns which you
don't aggregate. And you don't need "count(floor)" in
the select list - you already know it to be 1.

WHERE is perfectly legal, but only before the GROUP BY
clause.

select bldg, floor
from T
where type like '%Switch'
group by bldg, floor
having count(*) = 1

hth,
Urs Metzger