Mysql query to find count(id) of each month-year

Question

I am working in a refactoring of my code. And I am trying to get the result of a python script in a single query.

I tried to do this:

  select year(created), month(created), count(*) as total_id
  from repository_clinicaltrial
  group by id, year(created), month(created)
  order by id, year(created), month(created);

to get the count of ids of each MONTH-YEAR.

Using python i am doing this way:

dic_months = {1: "jan",
         2: "fev",
         3: "mar",
         4: "abr",
         5: "mai",
         6: "jun",
         7: "jul",
         8: "ago",
         9: "set",
         10: "out",
         11: "nov",
         12: "dez"
        }

def retorna_dic_report(yearI):

    yearN = yearI + 1


    query_report = 'select created from repository_clinicaltrial where created >= "%s-01-01" and created < "%s-01-01";' % (yearI,
                                                                                                                          yearN)


    db = MySQLdb.connect(host= host,
                  user= user,
                  passwd= pass,
                  db= db)
    cur = db.cursor()


    cur.execute(query_report)

    dic_result = {}
    for row in cur.fetchall():
            try:
               dic_result[dic_months[row[0].month]] = dic_result[dic_months[row[0].month]] + 1
            except:
               if row[0].month == 12:
               dic_result[dic_months[row[0].month]] = 1

    return dic_result

I want to use a single query that return the count of ids of each months in a range of years.

is it possible?

Do not include the `id` column in your GROUP BY and ORDER BY — Joakim Danielson, Jun 25 '19 at 15:59
To fix this it ,this link might help u [ count per month for year](https://stackoverflow.com/a/9888375/9347361) — Hiba T, Jun 25 '19 at 16:20
To fix this it,this link might help u [count per month for year](https://stackoverflow.com/a/9888375/9347361) — Hiba T, Jun 25 '19 at 16:23

score 1 · Answer 1 · answered Jun 25 '19 at 16:06

You want one row per year and month, so that should be what is in the group by. You also have id, which doesn't seem correct. So:

select year(created), month(created), count(*) as total_id
from repository_clinicaltrial
group by year(created), month(created)
order by year(created), month(created);

Note: if you are missing dates in a particular month, that will not be in your result set. I'm not sure if that is relevant to your question.

Mysql query to find count(id) of each month-year

1 Answers1