CODEWITHSUNDEEP
phpmysql
user3670605
user3670605

Reputation: 57

Counting renewals during count of subscription sold

I've a table in which write every subscription sold of my magazine, like this:

USERID | DATE | 

31 | 2011-09-22 | 
54 | 2011-09-22 | 
59 | 2011-09-23 | 
11 | 2011-10-02 | 
88 | 2011-10-05 |
31 | 2011-10-06 | 
17 | 2011-10-12 | 
54 | 2011-10-15 |
31 | 2011-11-05 |
54 | 2011-11-12 |

Now, for statistical purpose, i need to having an outcome in which i see, for every single month, how many subscriptions i've sold and how many users have already bought once this.

For instance, if we look the datas on top, i should have an outcome likie this:

DATE | SOLD | RENEWAL 
 09  | 3    | 0
 10  | 5    | 1
 11  | 2    | 2

I can to group the subscriptions sold monthly, but i can't add the "renewal" info.

  SELECT COUNT( * ) AS  sold, MONTH(date) FROM table_sold WHERE date >= CURDATE() - INTERVAL 13 MONTH GROUP BY YEAR( date ) , MONTH( date ) ORDER BY date ASC LIMIT 1,15

In this way i only have an outcome like this:

 DATE | SOLD
  09  | 3    
  10  | 5
  11  | 2

I've tried several option, with subquery, union and so on, but without successful. There is to consider that the table has 70.000 entries and a query with a hard scan could be a problem.

In your opinion is there a way in mysql or i've to take the idea to make it in php?

Upvotes: 0

Views: 134

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

Here is an idea. Look at the first date for each user. Then count renewals in every month that is not the first date:

select year(date), month(date), count(*) as sold,
       sum(case when date <> firstdate then 1 else 0 end) as renewals
from subscriptions s join
     (select userid, min(date) as firstdate
      from subscriptions s
      group by userid
     ) su
     on s.userid = su.userid
group by year(date), month(date)
order by year(date), month(date);

Upvotes: 1

Related Questions