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 |
|
chexone
Starting Member
6 Posts |
 Posted - 2005-12-31 : 15:53:12
|
| Hi there,I have 2 tables: ITEM (itemID, catID) and CATEGORY (catID, catName).I want to write a query that retrieves all the catNames with the number (count) of items from each category.Possible?Thx!! |
|
|
chexone
Starting Member
6 Posts |
Posted - 2005-12-31 : 16:34:52
|
| I answered my own question. Here it is:SELECT CATEGORY.*, COUNT(item.itemID) AS TotalFROM CATEGORY INNER JOIN ITEM ON CATEGORY.catID = ITEM.catIDGROUP BY category.CATID, category.catName, item.catID |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2005-12-31 : 20:36:59
|
| change INNER to LEFT OUTER and you'll also get categories that don't have itemsyou're fortunate that the inclusion of item.catID in the GROUP BY, and the use of "select star" in the SELECT list, had no deleterious effects -- normally those tactics will cause incorrect results or syntax errorsyou should change the SELECT columns and the GROUP BY columns so that they have have identical non-aggregate columnsrudyhttp://r937.com/ |
|
|
|
|
|
|
Category (item count)