Fifth Report Challenge: A client wants to create a report which shows a count of items within a specific item type.
These notes are intended to be used to help answer the associated Report Challenge that was released this week.
Take a look at the question below and think about how you would approach answering it:
A client wants to create a report which shows a count of items within a specific item type
Key points in this challenge
There are options for showing either the content of a table or summarising the content
How different functions can be used together or individually
It is possible to split overall counts by different facets
This example looks at a different table from the one we have looked at in many of the other report challenges. To build this report, we now need to find the table that relates to items.
For details on finding a table within the Koha database, please check our Help Centre.
Try to find the table that should be used with the report and then click the tab below to check the answer.
Which table does this report use?
items
Field to be added up
Now we have identified the table to work with, we need to determine what needs to be added to the report to allow the number of entries in the table to be counted, instead of being brought back individually. To do this, we need to pick a field that will contain an entry for every record on the items table.
We can see some of the fields in the table have a small key beside them. These fields are the primary key and foreign keys.
The primary key in the table contains a field that will always have unique entries, in this case, no two entries under itemnumber will ever be the same.
The foreign key refers to a field that is a primary key in another table, which can help when determining how to JOIN tables together in a report.
In this case, we want to use the primary key field, itemnumber, as because it is a primary key there will be content for every entry in the table and they will be unique.
Using a different field, for example the price field, would likely result in an inaccurate count for the number of items in a table, because not every item record will have price information on it. However, every item record will have an itemnumber on it.
How do we format the use of the COUNT function?
Based off of the reports that have been generated in other report challenges, first try to put together the format for reporting on itemnumbers and item types from the items table, without incorporating the COUNT function. Then click the dropdown below to see the correct format of the report.
Report format without COUNT
SELECT itemnumber, itype
FROM items
LIMIT 50
Once the general format of the report has been created, we can look at how to incorporate the COUNT function so that each individual itemnumber isn't brought back. Instead, each itemnmber will be counted and the total number of items in table will be shown in the report results.
The COUNT function should go directly before the field to be counted.
The report format now looks like this:
SELECT COUNT items.itemnumber, itype
FROM items
FROM items
The field to be counted should be encased in two brackets, the standard rounded brackets.
SELECT COUNT(items.itemnumber), itype
FROM items
The report results:
We can see that there is a column for the overall count of items on the table but only one item type has been brought back.
This is useful for getting an overall count of the number of entries in a table, but it can be more useful if the count is split up by a defined facet. This means you still get a count, but you get counts for each individual defined parameter. In this case, we will be looking at getting a count for each individual item type or how many items in an item type.
Using the COUNT and GROUP BY functions together.
We already have the item type field defined in the report. This is the facet we want to sort the count by and this can be achieved with the GROUP BY function.
We need to incorporate the GROUP BY function.
This is the current format of the report:
SELECT COUNT(items.itemnumber), itype
FROM items
And these are the results:
To add the GROUP BY function, put GROUP BY at the end of the report.
SELECT COUNT(items.itemnumber), itype
FROM items
GROUP BY
The facet to organise the count by should go after the GROUP BY statement.
SELECT COUNT(items.itemnumber), itype
FROM items
GROUP BY itype
The report will then bring back:
The itype facet can be replaced with any other facet. Try using a new table and a new facet to create another report with the COUNT and GROUP BY functions.