0

A group is defined by column a, b and c. Column x, y and z from each group are the same. Sample:

a|b|c|x|y|z| ....
1 1 1 p r s 
1 1 1 p r s 
1 1 1 p r s 
2 1 2 t u v
2 1 2 t u v

I am looking to achieve the following however without using aggregate function (max(t.x), ...)

select  t.a, t.b, t.c,count(*), t.x, t.y, t.z, ....
from t
group by t.a, t.b, t.c;

Is there any other function that can be used in the select statement to include columns x,y and z?

Would you rather use another join to add the descriptive column?

martez
  • 107
  • 1
  • 11
  • Why is your query tagged with both Netezza and SQLServer? Which are you using? Also, do you realise that `count(*)` is an aggregate function? –  Jun 11 '13 at 14:44
  • Mark dont you think that the question can be suited for both communities? – martez Jun 11 '13 at 18:32

1 Answers1

0

If the columns are the same within a group, just include them in the group by clause:

select  t.a, t.b, t.c,count(*), t.x, t.y, t.z, ....
from t
group by t.a, t.b, t.c, t.x, t.y, t.z

If you want a random row with the count, then use window functions:

select t.*
from (select t.*,
             count(*) over (partition by a, b, c) as cnt,
             row_number() over (partition by a, b, c order by (select NULL)) as seqnum
      from t
     ) t
where seqnum = 1

The order by (select NULL) is used in SQL Server. I'm not sure if it will work in Netezza. Any expression will work for the order by.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The problem with this approach would be that if one the descriptive column (t.x, t.y, t.z, ..) would differ from other columns of the same group it would result in a new group. In this case the bellow example would result in two groups instead of one.. `a|b|c|x|y|z| .... 1 1 1 p r s 1 1 1 p r s 1 1 1 p r sa` – martez Jun 11 '13 at 18:28
  • @martez . . . And if that were the case, then this statement in the question would not be true: "Column x, y and z from each group are the same. ". – Gordon Linoff Jun 11 '13 at 19:05
  • You are right. But in the case if an error occurs in x,y,z a new group/item will be added which is not ok. In the case where group by would be defined on just columns a,b,c this behavior would not occur. – martez Jun 11 '13 at 19:19