CODEWITHSUNDEEP
mysqlcount
soft genic
soft genic

Reputation: 2056

Number of Posts as per days in a month

There is a table Post in my database which contains posts of different users. What I wanna do is to create an sql query that'll return as per respective month the number of posts being made each day. Kindly let me know how can i do that generically in one query i can create multiple queries for all days but that is a worst case scenario. So I need expert's solution to this. Thanks

Expected output: (Query counts the number of posts for all the days in a respective month)

Day   :   Number of posts

1       :        20

2       :        25

3       :        10

4       :        17

.........................

30       :        6

Table Structure: ID | postid | post | date

Upvotes: 0

Views: 284

Answers (3)

nickhar
nickhar

Reputation: 20823

SELECT DAYOFMONTH(date), count(*) FROM Post
GROUP BY DAYOFMONTH(date)
ORDER BY DAYOFMONTH(date) ASC;

If you want to query for a specific month (say, February) then use this:

SELECT DAYOFMONTH(date), count(*) FROM Post
WHERE MONTH(date) = '2'
GROUP BY DAYOFMONTH(date)
ORDER BY DAYOFMONTH(date) ASC;

Note: Months are returned in number form where the MONTH() function is used.

EDIT: If you're looking to return counts for EVERY day in a given month, then I'd push you here - a great accepted answer to a similar question: How to get values for every day in a month

Upvotes: 2

sufleR
sufleR

Reputation: 2973

select DAYOFMONTH(date) as Day , count(*) as Number_of_posts
from table
group by DAYOFMONTH(date)

You should know that if table contains data from different months number of posts will be wrong.
So the group by should be by date and you should use date in selected instead of day of month.

Upvotes: 3

Patrick James McDougle
Patrick James McDougle

Reputation: 2062

SELECT date, COUNT(id) as number_of_posts FROM table_name GROUP BY date.

Upvotes: 1

Related Questions