Re: messy SELECT & GROUPing
sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote:
>
> Hi Ramiro,
> You need to concatenate the first five fields and group by the concatenated
> value. For eg, if your fields are named col1, col2, col3 .... col8, what you
> need is a SQL statment as follows:-
> select col1 || col2 || col3 || col4 || col5, count(*)
> from table
> group by 1
> having count(*) > 4
I think what he wants is to select 'unique' on the other three fields,
the above will give him a count of the duplicates on the other three
fields.
The following is not a single SQL, but it will work:
select a1,a2,a3,a4,a5,z1,z2,z3
from table
group by 1,2,3,4,5,6,7,8
into temp tmp_tbl with no log
select a1,a2,a3,a4,a5,count(*)
from tmp_tbl
group by 1,2,3,4,5
Hope that helps,
Douglas Wilson