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
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
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
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
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@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@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
-- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892
participants (3)
-
Alvaro Cornejo -
Andrew Fuerste-Henry -
Ma. Victoria H. Silva-Manuel