Thanks Mr. Andrew for your input. Below is the final SQL report that works:
SELECT count(*) 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>> > GROUP by url On Tue, Mar 28, 2023 at 9:28 PM Andrew Fuerste-Henry <andre...@dubcolib.org> wrote: > 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 > -- 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