Problem with sql query. Help me out?

Question

Hello,

The below query is working perfectly.
SELECT count(Comments.postingID) AS post,Comments.categoryID FROM Comments INNER JOIN Categories ON Comments.categoryID = Categories.categoryID GROUP BY Comments.categoryID

But, once I add, Categories.categoryTitle, leads to an error.In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = Categories,categoryTitle ]

SELECT Categories.categoryTitle, count(Comments.postingID) AS post,Comments.categoryID FROM Comments INNER JOIN Categories ON Comments.categoryID = Categories.categoryID GROUP BY Comments.categoryID

Please help me out.

Thanks

Answer 1

When you add any field to an already sumarized sql query, it should also be a sumarized field.



Try to imagine the data with food:



3 fruits

6 vegetables

2 chesse



it's counting how many of each category you have... in total of 11 items



now.. try to add the (title) name of each item



apple, 3 fruits

banana, 3 fruits

orange, 3 fruits



now.. you have 9 items.. just for fruit... that's why SQL won't let you do this.. because it doesn't have logic.



If you are trying to display some detail of each category... you will have to perform a second query to get this details...



of course you can get it, using views... but I think it will be more complicated.

Answer 2

Try this



SELECT Categories.categoryTitle, count(Comments.postingID) AS post,Comments.categoryID FROM Comments INNER JOIN Categories ON Comments.categoryID = Categories.categoryID GROUP BY Comments.categoryID, Categories.categoryTitle

Answer 3

If this is positive that the numbers are the left 3 characters in basic terms use the left function. If this isn't any longer positive in basic terms create a loop and truncate everytime one personality from the right area of the string and ask isnumeric and then splitt it. party a million: @myvalue_Numeric = Left(cope with, 3) @myvalue_String = correct(cope with, Len(cope with) - 3) party 2 For i = 0 to Len(cope with) If isnumeric(Left(cope with, Len(cope with) - i) then @myvalue_Numeric = Left(cope with, Len(cope with) - i) @myvalue_String = correct(cope with, Len(cope with) - i) end if next i Helmut