Group By Issues

[namtax]

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

[Dan_Bracuk]

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.

[namtax]

Hi dan,
yes the query runs fine....
i never knew brackets in the from clause where problematic.....

[cf_dev2]

> 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.

[namtax]

i mean, the query runs without the brackets, but i am still having the same issues..cheers

[cf_dev2]

> 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

[namtax]

hi cf_dev2...

thanks for response,

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

cheers

[cf_dev2]

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

[namtax]

ok, will give it a try, not sure im gonna be able to achieve what im after here tho..

thanks

[cf_dev2]

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?

[namtax]

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