I am having some issues trying to put together an SQL query of an Oracle 11g database, and I could use some help.
Let's say tableA is blog entries; tableC is comments for the blog entries; tableB is the associative table:
blogID blogTitle blogBody dateEntered
1 This is a test More text... 2016-05-20 11:11:11
2 More testing Still more! 2016-05-19 10:10:10
3 Third charm!! Blah, blah. 2016-05-18 09:09:09
commID userID userText dateEntered
10 Bravo I like it! 2016-05-20 11:21:31
11 Charlie I don't! 2016-05-20 11:31:51
12 Alpha Do it again! 2016-05-19 10:20:30
13 Bravo Still more? 2016-05-19 10:30:50
14 Charlie So, what? 2016-05-19 10:35:45
15 Bravo Blah, what? 2016-05-18 09:10:11
16 Alpha Magic number! 2016-05-18 09:11:13
I'm attempting to get blogID, blogTitle, blogBody, and a count of the number of comments for each blog entry. But, since I'm using to_char() for the date, and COUNT(commID) for the total number of comments, I'm getting "not a group by expression".
Here is a pseudo-SQL example of what I am trying.
SELECT a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS') as dateEntered, COUNT(c.commID) as total
FROM tableA a LEFT OUTER JOIN tableB b ON b.blog_ID = a.blog_ID
LEFT OUTER JOIN tableC c ON c.commID = b.commID
WHERE a.blogID = '1'
GROUP BY blogID, blogTitle, blogBody
ORDER BY to_date(dateEntered,'MM-DD-YYYY HH24:MI:SS') desc
I'm pretty sure it's something simple, but I am just NOT seeing it. Can you help?
You're using to_char in the select clause and to_date in the order by clause. Make them the same and you should be good to go, but then again, it is Oracle.
Wait, I should have looked closer.
to_char is not an aggregate function, so you have to include it in the group by clause.
Sorry about that.
I have aliased it, and tried using the alias and tried it using the column name, no change.
GROUP BY a.blogID, a.blogTitle, a.blogBody
GROUP BY a.blogID, a.blogTitle, a.blogBody, a.dateEntered --column name with table alias
GROUP BY a.blogID, a.blogTitle, a.blogBody, dateEntered --alias
I get varying error messages on each.
GROUP BY a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS')
AH! Never thought of that. I'll give it a shot.
Okay.. that seems to have fixed it, but the query is now breaking elsewhere.. of course.
Part of the problem (and don't ask me WHY the original developer did this, coz it makes ZERO sense) is that one of the columns (the body of the blog) is CLOB/BLOB, and if I include it in the group by, I get inconsistent datatypes, if I don't include it, I get the "not a group by expression".
*headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk*
That's why I recommend that people post their database questions in a forum that deals specifically with their database engine.
I'm not an Oracle guy, I could only answer your original question because it dealt with ANSI SQL.
Sorry.. I wasn't paying attention while typing. The to_date() _should_ be to_char().