How does COUNT work/defined

[Former Member]

It seems that COUNT takes a complete table column of values and returns the number of elements.  I need to craft a sql procedure, which, takes a table column of values, which returns a VARCHAR.  The issue i'm running into is with the definition of the function.  It seems, that in order to take a table as input, the function must also return a table. 

consider table:

mytable:

first     last               uri

-----     --------------     ------------------

andy     smith          my.pkg.sap

andy     smith          your.pkg.sap

andy     smith          this.pkg.com

fred       jones          pkg.something.core

fred       jones          my.pkg.sap

testing count on a cartesian table with procedure:

drop procedure test_counter;

create procedure test_counter() language SQLSCRIPT as

begin

  declare cnt,sze integer;

  declare uri_size,uri_cnt INTEGER;

  declare str VARCHAR(5000);

  declare str_num NVARCHAR(1023);

  declare uri_1,uri_2,uri_lnk VARCHAR(200);

  declare lt_lnk_uri TT_STRINGS;

  lt_first = select distinct("FIRST") as "FIRST_1" from test_count;

  lt_last = select distinct("LAST") as "LAST_1" from test_count;

  lt_cartesian = select first_1,last_1 from :lt_first, :lt_last;

  select first_1,last_1,count(pkg) from test_count,:lt_cartesian 

  where "FIRST" = first_1 and "LAST" = last_1 group by grouping sets ((first_1,last_1));

end;

call test_counter();

correctly returns distinct first,last combinations with their counts as I wanted.

        first_1     last_1                   count(pkg)

        -----     ----------               -------------------------

1     andy     smith               3

2     fred       jones               2

i need something like: select first,last,makestr(mytable.pkg) from myschema.mytable;

this would ideally return:

        first     last                   str

        -----     --------------          -----------------------------------------------

1     andy     smith               my.pkg.sap, you.pkg.sap, this.pkg.com

2     fred       jones               pkg.something.core, my.pkg.sap

How can I define the function signature to do this?

[Former Member]

AH, just found STRING_AGG (using sps12)

works perfectly, similar to COUNT(pkg), I simply use: STRING_AGG(pkg,',');

[SergioG_TX]

yes the string_agg function takes data from rows and concats them into a cell (as defined by the aggregation)

[SergioG_TX]

andy,

you can add a group by clause

select first, last, count(*)

from myTable

group by first, last