3 Replies Latest reply on Jun 24, 2007 10:51 AM by namtax

    Group By issues

    namtax Level 1
      I have a query which groups the result output to show all the songs contained on a specific album....I am also showing the amount of comments each song has by using COUNT in the select section of the query...

      thus..my code is

      <!---count number of songs--->
      <cfquery name="song_count" datasource="#application.datasource#">
      SELECT name_sng, id_sng, rec_rcd, COUNT(content_msg) as me_co
      FROM (record_rcd
      RIGHT OUTER JOIN song_sng
      on record_rcd.id_rcd = song_sng.id_rcd_sng)
      LEFT OUTER JOIN messages_msg
      ON song_sng.id_sng = id_sng_msg
      WHERE id_art_rcd = #id_art.id_art#
      GROUP BY name_sng

      and the output is

      <cfoutput query="song_count" group="rec_rcd">
      #name_sng# - #me_co# comments</a>

      the thing is, that i cannot get the songs to group by album properly

      see here


      if I remove the COUNT(content_msg) as me_co and the GROUP BY clause, the songs group by album as expected..

      just wondering what im doing wrong...any help would be massively appreciated