sglee
asked on
Displaying Category Name with Count
Hi, 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
</CFQUERY>
<SELECT NAME="SORTFIELD" onChange="this.form.submit
<OPTION VALUE="" selected>Select One
<CFOUTPUT query="GetCategory">
<OPTION VALUE="#ID#">#Category#</O
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
</CFQUERY>
<CFOUTPUT query="GetCategory">
<OPTION VALUE="#ID#">#Category#(#C
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
_agx_
That worked. Thank you.
That worked. Thank you.
ASKER
Now every category shows at least 1 even if there is no matching item in inventory table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Change to "SELECT COUNT(Category.ID)" from "SELECT COUNT(Category.Category)" did not make any difference.
I am using MS ACCESS.
I am using MS ACCESS.
ASKER
Let me check the data in Inventory table to see if there is anything that can throw the query off.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I 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#</O
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#</O
ASKER
@gdemaria,count(Inventory.CategoryID
ASKER
<OPTION VALUE="" selected>Show All </OPTION> <!------------------------
I think I need to put <CFIF> within the query.
I think I need to put <CFIF> within the query.
ASKER
Let me open up another question on this board as my original question has been answered!
Thanks.
Thanks.
>> 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!
>> COUNT(Category.ID),
@sglee - Gah! Sorry, that was a typo. I meant to write "Inventory.ID" in the sql... Good catch GD!
ASKER
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?