Re: Converting result set into string

[Chad Bourque]

Stefan,

Try this:

select d.catID, count(c.catID)
  from documents d
    join documents c
      on d.userID = c.userID
  where d.userID = 27345
    and d.docdate = '2009-10-06'
  group by d.catID;

If that gets you the values you are after (I'm just stabbing in the dark since I don't know your table structure), then you can try wrapping the two fields in a concat_ws:

select concat_ws('-', d.catID, count(c.catID))
  from documents d
    join documents c
      on d.userID = c.userID
  where d.userID = 27345
    and d.docdate = '2009-10-06'
  group by d.catID;

I'm not sure if that will work because I'm not too familiar with that function (I've never used it before). It may still complain about the invalid use of a group function. If so, you can try it this way:

select concat_ws('-', catID, cnt)
  from (
    select d.catID, count(c.catID) cnt
      from documents d
        join documents c
          on d.userID = c.userID
      where d.userID = 27345
        and d.docdate = '2009-10-06'
      group by d.catID);

HTH,
Chad