Copy link to clipboard
Copied
Hello, all,
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:
tableA
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
tableC
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
tableB
blogID commID
1 10
1 11
1 12
2 13
2 14
3 15
3 16
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?
V/r,
^_^
1 Correct answer
Try:
GROUP BY a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS')
Cheers
Eddie
Copy link to clipboard
Copied
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.
Cheers
Eddie
Copy link to clipboard
Copied
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.
Cheers
Eddie
Copy link to clipboard
Copied
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.
V/r,
^_^
Copy link to clipboard
Copied
Try:
GROUP BY a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS')
Cheers
Eddie
Copy link to clipboard
Copied
AH! Never thought of that. I'll give it a shot.
Thanks!
V/r,
^_^
Copy link to clipboard
Copied
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*
^_^
Copy link to clipboard
Copied
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.
Cheers
Eddie
Copy link to clipboard
Copied
Sorry.. I wasn't paying attention while typing. The to_date() _should_ be to_char().
V/r,
^_^

