-2

In my db, I have 2 tables: users and accounts. Every user bring several accounts. (users has UserID, and accounts has UserID, AccountID and createTime)

I need to get an SQL query that shows statistics about my users: a table that every row is how much accounts were created on every month, and the columns is the name of the users.

Can I do this?

Joe3322
  • 9
  • 2
  • What have you tried so far? From what you wrote, the `users` table does not need to even be part of the query because it just contains an ID. – Tim Biegeleisen Jan 28 '16 at 09:09
  • 1
    @TimBiegeleisen, Comeon ... He wants us to do the work for him. Didn't you see he said *I need to get an SQL query that shows* :) – Rahul Jan 28 '16 at 09:10
  • I don't even know how to start... @TimBiegeleisen . I don't know how to do a query with a changing number of columns – Joe3322 Jan 28 '16 at 09:10
  • Possible duplicate of http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – Tah Jan 28 '16 at 09:12
  • This question is for learning MySQL. I don't want you to do my job. I just invented 2 tables, in order to get a query and get the principle. I don't know how to do it. @Rahul – Joe3322 Jan 28 '16 at 09:12
  • It's not a learning place. Moreover, morally you should ask for help only when you have done sufficient effort from your end. – Rahul Jan 28 '16 at 09:13
  • Can you elaborate on what the relation between `UserID` and `AccountID` is? Will these always have a one-to-one relationship? – Tim Biegeleisen Jan 28 '16 at 09:16
  • @TimBiegeleisen the idea is that one user have many accounts. for now, I can do select count(AccountID) as num, UserID from accounts group by UserID but i need it per month, this is the problem – Joe3322 Jan 28 '16 at 09:18

2 Answers2

2

Something like the following should work for you:

SELECT UserId, DATE_FORMAT(CreatedTime, '%Y-%m'), COUNT(*) AS numAccountsPerUserPerMonth
FROM accounts
WHERE YEAR(createTime) = 2016
GROUP BY UserId, DATE_FORMAT(CreatedTime, '%Y-%m')
ORDER BY DATE_FORMAT(CreatedTime, '%Y-%m') DESC, UserId
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • How about same month in difference year? – hvnis Jan 28 '16 at 09:21
  • 2
    DATE_FORMAT(CreatedTime, '%Y-%m') @hvnis – Joe3322 Jan 28 '16 at 09:22
  • I understand this solution. It does solve it but this is not actually what I asked.. I asked if I can do a query that his columns are the userid and rows are month, and in every cell there's the number of accounts that were opend, I actually wrote something like that in the comments above... But thanks for your solution. – Joe3322 Jan 28 '16 at 09:27
  • @Joe3322 You are asking us for a pivot query, and if the number of users is very large, or not completely known, you will have to use dynamic SQL to do this. – Tim Biegeleisen Jan 28 '16 at 09:29
  • @TimBiegeleisen Great. Thanks!! – Joe3322 Jan 28 '16 at 09:31
  • Do you have a resonably small, fixed number of users? Or is the number of users unknown at design time? – Tim Biegeleisen Jan 28 '16 at 09:31
2

This should do what you want:

SELECT UserId, YEAR(createTime) as YEAR, MONTH(createTime) as MONTH, COUNT(*) AS accCounter
FROM accounts
GROUP BY UserId, YEAR(createTime), MONTH(createTime)

This will give you a count for how many accounts each user opened in a month.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
sagi
  • 40,026
  • 6
  • 59
  • 84