10 Replies Latest reply on Oct 29, 2007 3:53 PM by namtax

    Group By Issues

    namtax Level 1
      Hi all

      Im having a little issue with my query, and wondered if anyone could assist

      My query draws all messages made on a particular song, and also drags out any comments made about those specific messages

      <cfquery name="explained" datasource="#APPLICATION.DataSource#">
      SELECT name_usr, date_msg, content_msg, id_usr, id_msg, hot_msg, not_msg, username_msg, photo_usr, idmsg_msg, message_com, username_com, date_com, id_com, COUNT(message_com) as comma
      FROM (user_usr
      LEFT OUTER JOIN messages_msg
      ON user_usr.id_usr = messages_msg.id_usr_msg)
      LEFT OUTER JOIN song_com
      ON messages_msg.id_msg = song_com.id_msg_com
      WHERE id_sng_msg= '#id_sng.id_sng#'
      AND content_msg IS NOT NULL
      GROUP BY id_com
      ORDER BY (hot_msg-not_msg) DESC
      </cfquery>

      Here messages_msg is the table which contains messages made about a song,
      And song_com is the table that deals with any comments made about those specific message..

      If you see here, you can see how you can show/hide any comments made about a message..

      http://www.musicexplained.net/s/songs.cfm?a=jeff+buckley&s=Everybody+Here+Wants+You

      In the query, i am counting how many comments are made about a specific message "COUNT(message_com) as comma", and then placing this on the page to display how many....

      "show/hide #comma# comments on the message below....."

      However, if you look at the link above again, when you click to display comments for the second message "I dont even know which Bucks tune this is. Any chance uploading the vid link?"..three comments are displayed, but #comma# is only equal to 1....not 3

      wondering where im going wrong..

      hope this makes sense..

      thanks
        • 1. Re: Group By Issues
          Dan Bracuk Level 5
          Are you sure your query even ran? Since you are selecting many fields plus a sum, and grouping only on one field, it would crash if I tried to run it.

          The brackets in your from clause may be contributing to your problems as well.
          • 2. Re: Group By Issues
            namtax Level 1
            Hi dan,
            yes the query runs fine....
            i never knew brackets in the from clause where problematic.....
            • 3. Re: Group By Issues
              namtax Level 1
              i mean, the query runs without the brackets, but i am still having the same issues..cheers
              • 4. Re: Group By Issues
                cf_dev2 Level 1
                > i never knew brackets in the from clause where problematic.....

                Using parenthesis ( ) are not problematic. But they can effect the order by which the db evaluates your JOINs, which in turn may effect the query results.

                > SELECT name_usr, date_msg, content_msg, id_usr, id_msg, hot_msg, not_msg,
                > username_msg, photo_usr, idmsg_msg, message_com, username_com, date_com,
                > id_com, COUNT(message_com) as comma

                Think about what you're actually selecting. The query is selecting a large number of detailed fields, then saying COUNT the number of message_com values for each of those each combinations. Its not surprising the count is always 1.


                • 5. Re: Group By Issues
                  namtax Level 1
                  hi cf_dev2...

                  thanks for response,

                  how would you suggest i handle this?simplify the query?

                  cheers
                  • 6. Group By Issues
                    cf_dev2 Level 1
                    There are several ways you could approach it, but the basic idea is the same. If you want a COUNT of the number of comments per MessageID, then GROUP BY the MessageID and COUNT the number of comment id's.

                    psuedo - code

                    SELECT YourMessageTable.MessageID,
                    COUNT(YourCommentsTable.CommentID) as NumberOfComments
                    FROM
                    YourMessageTable LEFT JOIN YourCommentsTable
                    ON YourMessageTable.MessageID = YourCommentsTable.MessageID
                    WHERE ...
                    GROUP BY YourMessageTable.MessageID
                    • 7. Re: Group By Issues
                      cf_dev2 Level 1
                      > GROUP BY id_com

                      Most db's I've used wouldn't allow that query either. Usually all columns in the SELECT list, that are not included in the aggregate function, must be included in the GROUP BY

                      GROUP BY
                      name_usr,
                      date_msg,
                      content_msg,
                      ....rest of columns
                      • 8. Re: Group By Issues
                        namtax Level 1
                        ok, will give it a try, not sure im gonna be able to achieve what im after here tho..

                        thanks
                        • 9. Re: Group By Issues
                          cf_dev2 Level 1
                          It can be done. Whether you choose to use a separate query, QoQ, subquery or whatever depends on how you structure it. The point is you cannot get the correct counts if you're grouping by the wrong fields or too many fields. Make sense?

                          • 10. Re: Group By Issues
                            namtax Level 1
                            Thats cf..

                            Yes it does make sense......the thing which i think is complicating the issue is that I am grouping the output also...so its...

                            <cfoutput="explained">
                            message
                            <cfoutput>
                            comments about message
                            </cfoutput>
                            </cfoutput>

                            so I get the correct count of "comments about message" i.e. 3 but its only displaying one "comments about message" on screen at a time...

                            It late here, will have a go tommorrow and see how it goes

                            thanks for you help