Hello! The notation is table.column, so itype is a column in the items table. You can see the database schema here: https://schema.koha-community.org/ So you'd want to join in the items table to get your query to limit by itemtype: SELECT count(DISTINCT biblionumber) AS 'No. of Times Clicked', biblio.title AS 'Title', URL FROM linktracker JOIN biblio USING(biblionumber) JOIN items USING (biblionumber) WHERE date(timeclicked) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND items.itype=<<itype|itemtypes>> Hope that helps! Andrew ________________________________ From: Koha <koha-bounces@lists.katipo.co.nz> on behalf of Ma. Victoria H. Silva-Manuel <mavicsilva@gmail.com> Sent: Monday, March 27, 2023 7:36 PM To: Alvaro Cornejo <cornejo.alvaro@gmail.com> Cc: koha <koha@lists.katipo.co.nz> Subject: Re: [Koha] Click Tracker Thank you for responding, unfortunately it still didn't work. On Mon, Mar 27, 2023, 9:32 PM Alvaro Cornejo <cornejo.alvaro@gmail.com> wrote:
Hi Victoria
I don't recall koha's tables content, but in mysql the dot delimit column and table. Therefore, items.itype means column "items" from table "itype" and in your query you are not calling table itype.
So you can add a join with itype table or, if column "items" exist, in linktracker or biblio tables, you can use items.linktracker =<<itype|itemtypes>> or items.biblio=<<itype|itemtypes>>
Regards,
Alvaro
|----------------------------------------------------------------------------------------| Stay safe / Cuídate/ Reste sécurisé *7* Switch off as you go / Apaga lo que no usas / Débranchez au fur et à mesure. *q *Recycle always / Recicla siempre / Recyclez toujours P Print only if absolutely necessary / Imprime solo si es necesario / Imprimez seulement si nécessaire
Le lun. 27 mars 2023 à 00:31, Ma. Victoria H. Silva-Manuel < mavicsilva@gmail.com> a écrit :
I am using this SQL to generate Click Tracker report:
SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title', URL
FROM linktracker
JOIN biblio USING(biblionumber)
WHERE date(timeclicked) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND
<<and (yyyy-mm-dd)|date>>
GROUP BY url
I want to add a drop down list for item type. I tried adding items.itype=<<itype|itemtypes>> in WHERE part, but I'm getting the following error when I run the report:
The following error was encountered:
The database returned the following error: Unknown column 'items.itype' in 'where clause' Please check the log for further details.
What should I do?
-- 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
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha