Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
disciple
Starting Member
27 Posts |
 Posted - 2006-04-09 : 20:33:21
|
| im trying to display a category name in one column and then the number of books in that category in another column. SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS;Gives me an error saying something about category not being a single group function. What am I doing wrong? |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-09 : 20:47:19
|
| SELECT CATEGORY, COUNT(CATEGORY) AS [Category Total] FROM BOOKS GROUP BY CATEGORYSrinika |
 |
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-09 : 21:18:54
|
| That worked perfectly, thankyou very much. How come that works and mine doesnt? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-09 : 21:27:50
|
| Your query has an aggregate function (count) and no group by clause. The aggregate will cause it to return a single value for the result set. You are also trying to select category which will try to return multiple rows - this is the conflict that the error message is describing.Adding the group by clause causes the aggreagate to return a value for each group, It can also return the distinct values for the group (Category) which is why Srinika's query works.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-09 : 21:29:55
|
| Thankyou very much for your explanation. I believe I understand it now. |
|
|
|
|
|
|
count with 2 columns?