[Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

[SATISH]

HI

I have compiled Date wise daily transactions/circulation report which I had
asked in my previous mails. Thanks to Barton Chittenden and Mark Alexander
for quick response.
Please also advice me, how to add this in the sql wiki library.
1. Date wise daily Transactions (Issue, Renewal, Return) stats for a
specific month & year
---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
      DATE(datetime) AS DATE,
      SUM( IF(type = 'issue', 1, 0 )) as Issues,
      SUM( IF(type = 'renew', 1, 0 )) as Renewals,
      SUM( IF(type = 'return', 1, 0 )) as Returns,
      COUNT(statistics.type) AS 'Total Transactions'
FROM  statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>> AND  MONTH(datetime) =
<<Enter Month MM>>
GROUP BY DATE(datetime)

------------------------------------------------------------------------------------------------------------------------------------------------
Example : YYYY = 2017, MM = 3 (for March)

Result looks like:

DATEIssuesRenewalsReturnsTotal Transactions
2017-03-01 46 0 13 59
2017-03-02 43 0 9 52
2017-03-03 80 0 2 82
2017-03-04 25 0 5 30
2017-03-06 44 102 30 176
2017-03-07 30 52 46 128
2017-03-08 50 139 55 244
and I am looking help for my second query - i.e,

2.  Monthly transactions / circulation report for an entire year :

example: for the year 2017

Month |  Issues | Renewals | Returns
----------------------------------------------------------
January                55         45        50
February               50         45        35
so on .. until December


I did compile to some extent, but request some experts to  modify sql for
me as I desired.
sql is as follows;
----------------------------------------------------------------

SELECT monthname(datetime) AS month,
       year(datetime) AS year, count(*) AS count
FROM statistics
WHERE statistics.type IN ('issue') and year(datetime) = <<Year>>
GROUP BY month(datetime)

--------------------------------------------

Example for the YYYY = 2016, results looks like:

monthyearcount
July 2016 22
August 2016 41
September 2016 1447
October 2016 538
November 2016 1174
December 2016 977


 But I am looking in following format,

Month |  Issues | Renewals | Returns
----------------------------------------------------------
January                55         45        50

Please share your ideas.


With Thanks

Satish MV
Librarian
Govt. Engineering College, Hassan
Karnataka.
www.gechassan.ac.in