[Koha] SQL help

[Bernardo Gonzalez Kriegel]

On Wed, Dec 11, 2019 at 5:53 PM Kerrie Stevens <kerrie.stevens at ac.edu.au>
wrote:

> We have version 18.11.11 and I’ve looked through the reports library but
> can’t see anything that looks similar to be tweaked into what I need.
>

Try this, for year 2019

SELECT month, day, suc+uns AS total, suc AS succesful, uns AS unsuccesful
FROM (
       SELECT
               CASE WHEN A.m1  IS NULL THEN A.m2 ELSE A.m1  END AS month,
               CASE WHEN A.d1  IS NULL THEN A.d2 ELSE A.d1  END AS day,
               CASE WHEN A.suc IS NULL THEN 0    ELSE A.suc END AS suc,
               CASE WHEN A.uns IS NULL THEN 0    ELSE A.uns END AS uns
       FROM (
               SELECT * FROM
               (
                       SELECT month(time) AS m1, day(time) AS d1, count(*)
AS suc
                       FROM search_history
                       WHERE total > 0 AND year(time) = '2019'
                       GROUP BY m1,d1
               ) AS S
               LEFT JOIN
               (
                       SELECT month(time) AS m2, day(time) AS d2, count(*)
AS uns
                       FROM search_history
                       WHERE total = 0 AND year(time) = '2019'
                       GROUP BY m2,d2
               ) AS U
               ON S.m1 = U.m2 and S.d1 = U.d2
               UNION
               SELECT * FROM
               (
                       SELECT month(time) AS m1, day(time) AS d1, count(*)
AS suc
                       FROM search_history
                       WHERE total > 0 AND year(time) = '2019'
                       GROUP BY m1,d1
               ) AS SR
               RIGHT JOIN
               (
                       SELECT month(time) AS m2, day(time) AS d2, count(*)
AS uns
                       FROM search_history
                       WHERE total = 0 AND year(time) = '2019'
                       GROUP BY m2,d2
               ) AS UR
               ON SR.m1 = UR.m2 and SR.d1 = UR.d2
       ) AS A
       ORDER BY month,day
) AS B;


Hope that helps

Bernardo