[Koha] Click Tracker

[Ma. Victoria H. Silva-Manuel]

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


-- 
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892