0

I am currently working on a MySQL db with MySQL Workbench.

My objective is to retrieve the signups from a database to establish my company's KPIs on an Excel spreadsheet.

I wrote some sql queries that worked but I want to set up a very complete one in order to avoid using xxx different queries.

To get the signups for each month (based on 'created_at'), this makes the job:

SELECT year(u.created_at) year, monthname(u.created_at) month, COUNT(DISTINCT u.id) as 'New shoppers signups' 
FROM users u
GROUP BY year, month
ORDER BY u.created_at

But I also wanted to have the total of previous signups for each month

Jan : 12
Feb : 14 (12 + 2 new signups)
March : 22 (14 + 8 new signups)
...

Where I get the sum of all the previous signups

I was thinking about something like: 

DECLARE @month = '2012-01-01' //startdate
WHILE @month < curdate() 
BEGIN
    SELECT count(distinct u.id)
    WHERE u.created_at < @month
    dateadd(month, 1, @month) // incrementing to next month
END

But neither the while loop, the declare, set, or date function do work on MySQL Workbench.

I heard I have to declare procedures but I didn't have any more success...

I know I could use excel to get the result, but I want to improve my use of SQL and make this a very clear work.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Arnaud
  • 11
  • 4
  • Possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) – Shadow Jan 27 '16 at 14:04

1 Answers1

0

You are actually close to the answer. Take your results and make that an inner query. Then that is basis of an outer query using MySQL variables to accumulate for each row.

select
      pq.yearAdded, 
      pq.monthAdded, 
      pq.NewShoppers as 'New shoppers signups',
      @runBal := @runBal + pq.NewShoppers as TotalNewShoppers
   from
      ( SELECT 
              year(u.created_at) yearAdded, 
              monthname(u.created_at) monthAdded, 
              COUNT(DISTINCT u.id) as NewShoppers
           from
              users u
           GROUP BY 
              year(u.created_at), 
              monthname(u.created_at)
           ORDER BY 
              year(u.created_at), 
              monthname(u.created_at) ) pq,
      ( select @runBal := 0 ) sqlvars

I would just suggest having column names stay away from possible reserved words, such as Year, Month and other standard SQL commands and function names... otherwise you typically need to add tick-marks around the column names

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Works perfectly, thank you very much! Do you know if the excel file will be updated automatically, knowing that I import the datas using odbc? – Arnaud Jan 27 '16 at 14:15
  • @Arnaud, glad it worked. It SHOULD via ODBC as its just columns Excel would be querying this statement anyhow from whatever connection you have setup. Also, as a newbie, take a look at HELP -> TOUR for site etiquette.. Have a great day – DRapp Jan 27 '16 at 14:45
  • To go a step further, how would you do to get the datas per month but from different tables (eg merchants, requests,...) in order to get the same kind of records? – Arnaud Jan 27 '16 at 14:55
  • @Arnaud, post that as a new question and show the basis of the table getting merchants and requests and the aggregate group by. I am sure it would be similar. Let me know when posted and I'll look at that too. – DRapp Jan 27 '16 at 14:58