CODEWITHSUNDEEP
sqlmysqlstored-procedurescount
Wartin
Wartin

Reputation: 1965

Progressive count using a query?

I use this query to

SELECT userId, submDate, COUNT(submId) AS nSubms
FROM submissions
GROUP BY userId, submDate
ORDER BY userId, submDate

obtain the total number of submissions per user per date.
However I need to have the progressive count for every user so I can see how their submissions accumulate over time.

Is this possible to implement in a query ?

EDIT: The obtained table looks like this :

  userId submDate nSubms
  1       2-Feb    1
  1       4-Feb    7
  2       1-Jan    4
  2       2-Jan    2
  2       18-Jan   1

I want to produce this :

  userId submDate nSubms  progressive
  1       2-Feb    1           1
  1       4-Feb    7           8
  2       1-Jan    4           4
  2       2-Jan    2           6
  2       18-Jan   1           7

EDIT 2 : Sorry for not mentioning it earlier, I am not allowed to use :

Upvotes: 2

Views: 2821

Answers (3)

Thomas
Thomas

Reputation: 64655

Select S.userId, S.submDate, Count(*) As nSubms
    , (Select Count(*)
        From submissions As S1
        Where S1.userid = S.userId
            And S1.submDate <= S.submDate) As TotalSubms
From submissions As S
Group By S.userid, S.submDate
Order By S.userid, S.submDate

Upvotes: 1

bobince
bobince

Reputation: 536547

You can use a self-join to grab all the rows of the same table with a date before the current row:

SELECT s0.userId, s0.submDate, COUNT(s0.submId) AS nSubms, COUNT (s1.submId) AS progressive
FROM submissions AS s0
JOIN submissions AS s1 ON s1.userId=s0.userId AND s1.submDate<=s0.submDate
GROUP BY s0.userId, s0.submDate
ORDER BY s0.userId, s0.submDate

This is going to force the database to do a load of pointless work counting all the same rows again and again though. It would be better to just add up the nSubms as you go down in whatever script is calling the query, or in an SQL variable, if that's available in your environment.

Upvotes: 2

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

The Best solution for this is to do it at the client.
It's the right tool for the job. Databases are not suited for this kind of task

Upvotes: 1

Related Questions