Hi Victoria, This sounds like something COALESCE could do, a function I only learned about from this Koha US video: https://www.youtube.com/watch?v=35UvrpcYFFA Otherwise you could also do a convoluted IF() expression, since it sounds like the issue is working around a value that is sometimes null and sometimes not. Like: IF(borrowers.surname IS NOT NULL, CONCAT(borrowers.firstname, ' ',borrowers.surname), 'anonymous') You have no "FROM" statement in your SQL query so something must be missing, presumably "FROM linktracker" which connects the biblio and borrowers tables. Anyways, you probably want to JOIN these tables in such a way that even the records with no borrowernumber (e.g. anonymous users) are included. So ...JOIN biblio USING (biblionumber) LEFT JOIN borrowers USING (borrowernumber) A LEFT JOIN retains every row so far in the record set, but connects them to the following table if possible. So it should work whether the rest of your query has a borrowernumber or not. You said your query is working if you just delete the CONCAT() though, which seems odd to me, but if that's so then the LEFT JOIN probably is not necessary. Best, ERIC PHETTEPLACE Systems Librarian, Libraries (he/him) ephetteplace@cca.edu | o 510.594.3660 (cca) 5212 Broadway | Oakland, CA | 94618 CCA is situated on the traditional unceded lands of the Ohlone peoples. Black-owned bookstores in Oakland: Ashay by the Bay <https://ashaybythebay.com/>, Marcus Books <https://www.facebook.com/marcus.books/> :(){ :|: & };: On Sun, Jan 31, 2021 at 6:25 PM Ma. Victoria H. Silva-Manuel < mavicsilva@gmail.com> wrote:
Good day.
I am using the SQL report below to track clicks from tag 856$u
SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title',
CONCAT(borrowers.firstname, ' ',borrowers.surname) AS 'Patron', URL FROM linktracker
JOIN biblio USING(biblionumber)
JOIN borrowers USING(borrowernumber)
WHERE date(timeclicked) BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY url
The above report only shows the links clicked by log in users. When I delete the concat, it shows the links clicked by all users but does not show the names of logged in users. Is there a way to create a report that will show links clicked by all users and show the names if the users are logged in?
Thanks.
-- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892 _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha