How to get row count per day in sqlalchemy?

Question

I have following simple table,

ID    DateTime          Category
--  --------            ----------   
1   2018-05-12 5:05        1
2   2018-05-12 5:10        1
3   2018-05-12 6:25        2
4   2018-05-13 7:40        1
5   2018-05-14 8:50        4

I want to count the number of entries per day for each category, so result should be like,

Day          Category   Count
---------    --------  -------
2018-05-12       1        2
2018-05-12       2        1
2018-05-13       1        1
2018-05-14       4        1

How can i do this in sqlalchmey?

Edit: Database I'm using is PostgreSQL

Edit: Columns and their specific types are as following, ID - Integer, Category - Integer, DateTime - DateTime

Please provide table/model definitions and describe what you've tried and what part are you having trouble with. Note that date/time functions are usually a bit DBMS specific, so please include what DBMS are you using. — Ilja Everilä, Oct 08 '18 at 10:16
Also, have you read ["Group by & count function in sqlalchemy"](https://stackoverflow.com/questions/1052148/group-by-count-function-in-sqlalchemy)? — Ilja Everilä, Oct 08 '18 at 10:17
I updated the question with the information. Difficult part is you can't just group by using DateTime column. You need to do it by date, but column type is datetime. My real problem is more difficult than this, in it I want to group by weeks. — Sameera Kumarasingha, Oct 08 '18 at 10:25
I think ["How to group by week in postgresql"](https://stackoverflow.com/questions/36024712/how-to-group-by-week-in-postgresql) and ["Group by hour in SQLAlchemy?"](https://stackoverflow.com/questions/2113448/group-by-hour-in-sqlalchemy) will get you started. Just remember that in SQLAlchemy you can create any function expression using `sqlalchemy.func`. — Ilja Everilä, Oct 08 '18 at 10:33

score 1 · Answer 1 · answered May 27 '20 at 06:05

Since you didn't mention rows with 0 count, may be this can help you

def getRange(start, end, aggregate):
        query = db.select([
                func.max(Simulation.timestamp).label('timestamp'),
                func.sum(Simulation.PVPowerOutput).label('PVPowerOutput'),
                func.sum(Simulation.ACPrimaryLoad).label('ACPrimaryLoad')\
            ])\
            .where(Simulation.timestamp >= start)\
            .where(Simulation.timestamp <= end)\

        if aggregate == 'hourly':
            # do nothing
            query = query.group_by(Simulation.timestamp)
        elif aggregate == 'daily':
            # group by date
            query = query.group_by(func.date(Simulation.timestamp))
        elif aggregate == 'monthly':
            # group by month
            query = query.group_by(func.date_part('month', Simulation.timestamp))
        else:
            raise ValueError('invalid aggregation')
        return [dict(r) for r in db.session.execute(query).fetchall()]

score 1 · Answer 2 · answered May 27 '20 at 10:20

the simplest way is to do the following (without categories):

from sqlalchemy import text

q = text(
      """
      SELECT d.date, count(se.id)
      FROM (
        SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
        FROM generate_series(0, <NUMBER_OF_DAYS>, 1) AS offs
     ) d 
     LEFT OUTER JOIN some_table se 
       ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
     GROUP BY d.date;
     """
)
session.execute(q).all()

S-Man · Answer 3 · 2018-10-08T13:56:47.597

demo:db<>fiddle

Since I am not really into SQLAlchemy I can show you the pure Postgres way:

SELECT datetime::date, category, COUNT(*)
FROM simple_table
GROUP BY datetime::date, category

There is no problem to do it with weeks using EXTRACT (https://www.postgresql.org/docs/current/static/functions-datetime.html)

WITH weeks AS (
    SELECT EXTRACT(week from datetime) as week, category FROM simple_table 
)
SELECT week, category, COUNT(*)
FROM weeks
GROUP BY week, category

How to get row count per day in sqlalchemy?

3 Answers3