Re: distinct and counting

In article <3aiso9INN6ac_at_oasys.dt.navy.mil>, wybranie_at_oasys.dt.navy.mil (Suzanne Wybraniec) writes:
>
> I would like to query two fields in a table, and produce a result without
> printing the duplicates. The ways I've tried to structure the select
> statement all meet with errors.
>
> Table data samples:
> BLDG ROOM
> 1 A
> 1 A
> 1 B
> 1 C
> 1 C
> 1 C
> 2 A
> 2 B
> 2 B
>
> I want a report that looks like:
> BLDG ROOM (no heading on this column)
> 1 A 2 pieces are located here
> 1 B
> 1 C 3 pieces are located here
> 2 A
> 2 B 2 pieces are located here

Try this

select bldg, room, to_char(count('x')) || ' pieces are located here' from table
group by bldg, room

>
> Thanks,
> Suzanne
>
> Suzanne Wybraniec (Northern Virginia) wybranie_at_oasys.dt.navy.mil
> 11405 Great Meadow Dr. David Taylor Model Basin
> Reston, Virginia 22091 USA Carderock Division Headquarters
> Naval Surface Warfare Center
> Bethesda, Maryland 20084 USA

-- 
--------------------------------------------------------------------------------
Steven Champness                   champs_at_cnb07v.hhcs.gov.au
Dept of Human Services & Health    Brisbane, Queensland, Australia
The opinions expressed above were found in a box of breakfast cereal