[Koha] Click Tracker

[Andrew Fuerste-Henry]

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