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-boun...@lists.katipo.co.nz> on behalf of Ma. Victoria H. Silva-Manuel <mavicsi...@gmail.com> Sent: Monday, March 27, 2023 7:36 PM To: Alvaro Cornejo <cornejo.alv...@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.alv...@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 < > mavicsi...@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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
