jozii jozii - 29 days ago 12
SQL Question

Sort data by month, then year

Table Folder

Column | Type | Modifiers
-------------------+--------------------------+---------------------------------
ID | integer | not null default
Name | character varying | not null
Size | bigint | not null
Timestamp | timestamp with time zone |


attempting to get a count for all files uploaded in 2014. And monthly count for that same year.

SELECT COUNT(*) FROM "File" WHERE "Timestamp" > '2014-01-01 21:53:23+08'

Answer
SELECT TO_CHAR(Timestamp, 'Mon') AS month,
       COUNT(*) AS fileCount
FROM File
WHERE EXTRACT(YEAR FROM Timestamp) = 2014
GROUP BY TO_CHAR(Timestamp, 'Mon')

If you wanted a report which shows a monthly breakdown across multiple years, then you can slightly modify the above query:

SELECT TO_CHAR(Timestamp, 'Mon') AS month,
       EXTRACT(YEAR FROM Timestamp) AS year,
       COUNT(*) AS fileCount
FROM File
WHERE EXTRACT(YEAR FROM Timestamp) IN (2014, 2015, ...)
GROUP BY TO_CHAR(Timestamp, 'Mon'),
         EXTRACT(YEAR FROM Timestamp)