Displaying Category Name with Count

Category TableInventory TableCategory DropdownHi,
 I have two tables - Inventory and Category.
 As seen in the screenshots Inventory has items and each item belongs to a Caegory.
In the SELECT drop-down, I am listing Category Names from the Category table in an alphabetically order.
            <CFQUERY NAME="GetCategory" datasource="DSN">
                  SELECT      *
                     FROM         Category
            ORDER BY Category

                  <SELECT NAME="SORTFIELD" onChange="this.form.submit()">
                  <OPTION VALUE="" selected>Select One
                  <CFOUTPUT query="GetCategory">
                  <OPTION VALUE="#ID#">#Category#</OPTION>

What I would like to do is to display the count along with Category Name in the drop-down.
For example "Christmas - Ornaments (7)" "Easter (11)"

So this is how I modified the query and SELECT statement, but it generates an error:

            <CFQUERY NAME="GetCategory" datasource="DSN">
                  SELECT      Category.Category, Category.ID, Count(Category.Category) as CategoryCount
                     FROM         Category, Inventory
            WHERE Inventory.CategoryID = Category.ID
            GROUP BY Category.Category
            ORDER BY Category.Category

      <CFOUTPUT query="GetCategory">
      <OPTION VALUE="#ID#">#Category#(#CategoryCount#)</OPTION>

Error Message: "You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function."

Can you help?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You have to include every column in your group by, the ID is not included..

         SELECT      Category.Category, Category.ID, Count(Category.Category) as CategoryCount
                     FROM         Category, Inventory
            WHERE Inventory.CategoryID = Category.ID
            GROUP BY Category.Category
            ORDER BY Category.Category

            GROUP BY Category.Category
            GROUP BY Category.Category, Category.CategoryID

Or my preferred formatting...

SELECT  c.Category
      , c.ID
	  , Count(c.Category) as CategoryCount
FROM Category C
   inner join Inventory inv on inv.categoryID = c.ID
GROUP BY c.Category, C.ID
ORDER BY c.Category

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sgleeAuthor Commented:
Category with Count
The code with your own formatting  worked great except it only lists the categories that have more than 1 item in Inventory.
Can you list all the categories regardless of the Count?
Yes, replace your INNER JOIN:

   FROM   Category, Inventory
   WHERE Inventory.CategoryID = Category.ID

with an OUTER JOIN:

   FROM   Category LEFT JOIN Inventory ON Inventory.CategoryID = Category.ID

That will give you all categories, even if there's no matching inventory record.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

sgleeAuthor Commented:
That worked. Thank you.
sgleeAuthor Commented:
CategoryNow every category shows at least 1 even if there is no matching item in inventory table.
Which db?  In SQL Server you do a count of matching id's in the Inventory table. So instead of

SELECT COUNT(*), ..... etc                      <=== count rows


SELECT  COUNT(Category.ID),  .... etc       <=== count matching inventory records
sgleeAuthor Commented:
Change to "SELECT  COUNT(Category.ID)" from "SELECT  COUNT(Category.Category)" did not make any difference.
I am using MS ACCESS.
sgleeAuthor Commented:
Let me check the data in Inventory table to see if there is anything that can throw the query off.
sgleeAuthor Commented:
I checked the records in Inventory table and each item had a category ID that is defined in the Category table.
There were no extra records with missing data period.
I hand counted  # of items in each category in Inventory table and the numbers that I added up matched against those produced by the query.
If you want the number to reflect the quantity of inventory items, you have to count( ) the inventory table, not the category table

sgleeAuthor Commented:
Category Dropdown with Display AllI changed the drop-down code back to "displaying categories that have items in the inventory".
Now my question is how can I add the word "Display All Category" on the top of the drop down menu, maintaining the ability to display categories with number of items like on the screenshot?
You can leave the left join, just change the count to counting inventory items instead of records...   count(Inventory.CategoryID)

To add a display all option, its the same thing as your select option, just change the text and the value is empty..

 <SELECT NAME="SORTFIELD" onChange="this.form.submit()">
       <OPTION VALUE="" selected>Show All </OPTION>  <!-------------------------
       <CFOUTPUT query="GetCategory">
                  <OPTION VALUE="#ID#">#Category#</OPTION>
sgleeAuthor Commented:
Category Dropdown@gdemaria,
 count(Inventory.CategoryID) (that you posed ID: 41146987) worked!
sgleeAuthor Commented:
<OPTION VALUE="" selected>Show All </OPTION>  <!------------------------- displays the text "Show All", but it does not display all items from the inventory  table.
I think I need to put <CFIF> within the query.
sgleeAuthor Commented:
Let me open up another question on this board as my original question has been answered!
>> In SQL Server you do a count of matching id's in the Inventory table.
>>  COUNT(Category.ID),

@sglee - Gah! Sorry, that was a typo.  I meant to write "Inventory.ID" in the sql...  Good catch GD!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.