Can a field in the database be set up to display results of a select statement?

Question

For example lets say I have a field that lists a category an item belongs to. Could I then have another field that will display the results of a select statement that counts the number of items that belong to Category A?

Answer 1

A field of a database merely stores values. It doesn't display anything.

Those can be quite literally almost anything (i.e. Integers, Decimals, Floats, date/time, blobs, etc.).

So, in theory one could run a query which stores information that is gathered together into a field in a database.

Of course, you would have to run another query that simply selects from the field that you stored the results of a query so that it can be displayed using your choice of whatever software you'd like.

An example of a selection into a field would be like this:

INSERT INTO (results)

SELECT myfield1 + "," +  myfield2 + "," + myfield3

Edited to Add:

SELECT COUNT(category) WHERE category LIKE mycategory%

Of course, you'd have to replace the text "mycategory" with whatever you wanted to search for.

Then again, to tally all of the categories that are the same in a given table, you'd have to use a GROUP BY SQL statement, like this:

SELECT category, COUNT(category)

GROUP BY category

Answer 2

Sure, add that field to the table.  Make it so that everytime you add a new record for the category table, it pulls the number you have, adds 1, and updates the table to reflect that.  Then also in your program when you delete, have it pull that number, subtract 1, and update the table.

If you have it so that it just does a query everytime you or someone else goes to that page to see how many items are listed under that category, once it gets big enough it will prove to be a huge consumption of resources.

Answer 3

You can store the value of a count in a field.  But that's typically a poor design choice, since every time you add a record your table will become invalid.

What you probably should be doing is running the query to do your record count when, and each time, you need the information.  That way you're up to date at all times.

If you're never going to be adding or deleting items, then it's probably acceptable to include a hard value for the record count.  But that's pretty unlikely scenerio.