# Top titles by material type sql issue
**URL:** https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970
**Category:** Polaris
**Tags:** sql
**Created:** [June 23, 2026, 4:53pm UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970 "2026-06-23T16:53:38Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@lmurff](https://forum.innovativeusers.org/u/lmurff)
#### Post date: [June 23, 2026, 4:53pm UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970/1 "2026-06-23T16:53:38Z")
So I cribbed this SQL to find top titles and does appear to be working but I can not figure out why the reports circ count doesn’t always match Polaris. Could it be unique patrons or no renewals? If anyone has suggests, it would be much appreciated 🙂
Here is my code:
SELECT TOP 40 – Pulls the top 40 titles, adjust as needed
br.BibliographicRecordID AS [BibliographicRecordID],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
material.numValue AS [MaterialTypeID],
mat.Description AS [Material Type],
shelfloc.numValue AS [ShelfLocationID],
sl.Description AS [Shelf Location],
COUNT(shelfloc.numValue) AS [Circ Count]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN – Get the MaterialTypeID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)
LEFT JOIN – Get the ShelfLocationID if it’s there
PolarisTransactions.Polaris.TransactionDetails shelfloc WITH (NOLOCK)
ON (th.TransactionID = shelfloc.TransactionID AND shelfloc.TransactionSubTypeID = 296)
INNER JOIN – Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN – Get item record information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)
LEFT JOIN – Get bibliographic record information
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN – Get material type information
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)
LEFT JOIN – Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue)
WHERE – Check outs
th.TransactionTypeID = 6001
AND – Adjust dates as needed
th.TranClientDate BETWEEN ‘2025-07-01 00:00:00.000’ AND ‘2026-06-23 23:59:59.999’
AND – Material type
mat.MaterialTypeID = 110
AND – Exclude deleted bibs
br.BibliographicRecordID IS NOT NULL
GROUP BY
br.BibliographicRecordID,
br.BrowseTitle,
br.BrowseAuthor,
material.numValue,
mat.Description,
shelfloc.numValue,
sl.Description
ORDER BY
COUNT(shelfloc.numValue) DESC
---
### Author:  [@Phil\_Agnew](https://forum.innovativeusers.org/u/Phil_Agnew)
#### Post date: [June 23, 2026, 6:46pm UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970/2 "2026-06-23T18:46:45Z")
I initially suspected there was some sort of JOIN related issue, but didn’t catch it on my initial read… but if we display the output without grouping it for the count, we can clearly see that’s what’s going on:
So, it was just a matter of identifying the source of the problem… and of course it’s near the end of the query (isn’t it always?). It’s your LEFT JOIN for the Shelf Description. The _ShelfLocationID_s are repeated for every _OrganizationID_ in that uses them, so you need to get the _Item Assigned Branch ID_ from the _TransactionDetails_ and add that to your _ON_ statement for the JOIN:
```sql
LEFT JOIN --Get Item AssignedBranchID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails ab WITH (NOLOCK)
ON (th.TransactionID = ab.TransactionID AND
ab.TransactionSubTypeID = 125)
LEFT JOIN --Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue AND sl.OrganizationID = ab.numValue)
```
The complete query with those additions would look like this:
```sql
SELECT TOP 40 -- Pulls the top 40 titles, adjust as needed
br.BibliographicRecordID AS [BibliographicRecordID],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
material.numValue AS [MaterialTypeID],
mat.Description AS [Material Type],
shelfloc.numValue AS [ShelfLocationID],
sl.Description AS [Shelf Location],
COUNT(shelfloc.numValue) AS [Circ Count]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the MaterialTypeID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)
LEFT JOIN -- Get the ShelfLocationID if it’s there
PolarisTransactions.Polaris.TransactionDetails shelfloc WITH (NOLOCK)
ON (th.TransactionID = shelfloc.TransactionID AND shelfloc.TransactionSubTypeID = 296)
LEFT JOIN --Get Item AssignedBranchID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails ab WITH (NOLOCK)
ON (th.TransactionID = ab.TransactionID AND
ab.TransactionSubTypeID = 125)
INNER JOIN -- Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN -- Get item record information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)
LEFT JOIN -- Get bibliographic record information
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN -- Get material type information
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)
LEFT JOIN -- Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue AND sl.OrganizationID = ab.numValue)
WHERE -- Check outs
th.TransactionTypeID = 6001
AND -- Adjust dates as needed
th.TranClientDate BETWEEN '2025-07-01 00:00:00.000' AND '2026-06-23 23:59:59.999'
AND -- Material type
mat.MaterialTypeID = 110
AND -- Exclude deleted bibs
br.BibliographicRecordID IS NOT NULL
GROUP BY
br.BibliographicRecordID,
br.BrowseTitle,
br.BrowseAuthor,
material.numValue,
mat.Description,
shelfloc.numValue,
sl.Description
ORDER BY
COUNT(shelfloc.numValue) DESC
```
Give that a shot and see if the results match up any better for you!
---
### Author:  [@lmurff](https://forum.innovativeusers.org/u/lmurff)
#### Post date: [June 23, 2026, 7:26pm UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970/3 "2026-06-23T19:26:06Z")
Thank you! That definitly helped 🙂
---
### Author:  [@wesochuck](https://forum.innovativeusers.org/u/wesochuck)
#### Post date: [June 24, 2026, 12:13am UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970/4 "2026-06-24T00:13:00Z")
@lmurff you didn’t mention if the counts were too high or too low. @Phil_Agnew offered some good improvements, but a few other considerations:
> [@Phil\_Agnew](#):
>
> `COUNT(shelfloc.numValue) `
I think that this will still cause NULL shelf location item records to be **excluded** from the counts.
> [@Phil\_Agnew](#):
>
> ```auto
> LEFT JOIN -- Get item record information
> Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
> ON (cir.ItemRecordID = item.numValue)
>
> LEFT JOIN -- Get bibliographic record information
> Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
> ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
>
> ```
This will also end up requiring that there is an item record **still** in the system. So, if you deleted the item record, it **would be excluded** from the counts.
---
### Author:  [@lmurff](https://forum.innovativeusers.org/u/lmurff)
#### Post date: [June 25, 2026, 4:52pm UTC](https://forum.innovativeusers.org/t/top-titles-by-material-type-sql-issue/2970/5 "2026-06-25T16:52:59Z")
Thanks for all the help! The numbers were too low. I found out the issue was titles on multiple shelves. So I was able to make the connection on material type and take out the shelf location code. Thanks again, I really appreciate it