1

I want to count all new students in a given month from ten years ago to today and put that information into a table, separated month by month.

I have a mysql users table with the date the user enrolled in a timestamp format.

So, I've got something like this: (the format is dd-mm-yyyy)

Unix --------------- readable date  
1528743811 --------- 11-6-2018  
1528740914 --------- 11-6-2018  
1528740347 --------- 11-6-2018  
1528733661 --------- 11-6-2018  
1528676301 --------- 10-5-2018  
1528657978 --------- 10-5-2018  
1528508065 --------- 8-5-2018   
1528492266 --------- 8-4-2018
etc..

How may I do the counting? (I would appreaciate even an idea of where to start, or how to actually formulate the problem, even in seudocode).

I expect something like this:

4-2018  --------- 1
5-2018  --------- 3
6-2018  --------- 4

Please, if you think that the question syntaxis can be improved let me know,thanks! (english isn't my primary language).

Also, If you want to downvote, or vote the question to be closed or deleted, please leave me a comment about why, so I can improve my questions. Thanks.

Rosamunda
  • 14,620
  • 10
  • 40
  • 70
  • What have you tried doing thus far? Please post your code attempts – Derek Pollard Jun 12 '18 at 03:10
  • Try to group by? something like this? https://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql – Isaac Jun 12 '18 at 03:14
  • Thanks for your comment @Derek, I haven't done much, actually. I've sit down, got the dates from the database in a query, and started to think about how to actually arrive to the answer. I got stuck, that's why even seudocode will be appreciated, I'm not sure on how to start. – Rosamunda Jun 12 '18 at 03:15
  • Thanks for your comment @Isaac, that's actually a pretty good idea!! – Rosamunda Jun 12 '18 at 03:17
  • Are you really storing both of those columns? – Tim Biegeleisen Jun 12 '18 at 03:20
  • Post the real thing, your attempt sql code and the schema. That way you can improve the question. – Mawia HL Jun 12 '18 at 03:21

1 Answers1

2

You can do all this in SQL with FROM_UNIXTIME() and GROUP BY:

SELECT 
    FROM_UNIXTIME([Unix], '%M %Y') AS Label, 
    COUNT(*) AS Count 
FROM [table] 
GROUP BY MONTH(FROM_UNIXTIME([Unix])), YEAR(FROM_UNIXTIME([Unix]))
WHERE ADDDATE(FROM_UNIXTIME([Unix]), INTERVAL 10 YEARS) >= CURDATE()

Or if that is too many FROM_UNIXTIME, you can subquery it.

zambonee
  • 1,599
  • 11
  • 17