CODEWITHSUNDEEP
mysqlsqlcountgroup-by
ditto
ditto

Reputation: 6277

SQL: Increment for every GROUP BY

I'm wanting to return the total number of times an exerciseID is listed, but filter it so each exerciseID may only be increment once per a date. For this reason I believe I cannot do a group by date.

id | exerciseid | date
1  | 105        | 2014-01-01 00:00:00
2  | 105        | 2014-02-01 00:00:00
3  | 105        | 2014-03-11 00:00:00
4  | 105        | 2014-03-11 00:00:00
5  | 105        | 2014-03-11 00:00:00
6  | 127        | 2014-01-01 00:00:00
7  | 127        | 2014-02-02 00:00:00
8  | 127        | 2014-02-02 00:00:00

// 105 = 5 total rows but 3 unique
// 127 = 3 total rows but 2 unique

$db->query("SELECT exerciseid as id, sum(1) as total
            FROM `users exercises` as ue
            WHERE userid = $userid
            GROUP BY exerciseid
            ORDER BY date DESC");

Current Output:

Array
(
    [id] => 105
    [date] => 2014-05-06
    [total] => 5
)
Array
(
    [id] => 127
    [date] => 2014-05-06
    [total] => 3
)

As you can see it's not merging the rows where the date and exerciseid are the same.

Expected Result:

Array
(
    [id] => 105
    [date] => 2014-05-06
    [total] => 3
)
Array
(
    [id] => 127
    [date] => 2014-05-06
    [total] => 2
)

Upvotes: 0

Views: 142

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35573

for V2.0 question:

select
        exerciseid
      , count(distinct date) as exercise_count
from user_exercises
group by
        exerciseid
;

| EXERCISEID | EXERCISE_COUNT |
|------------|----------------|
|         54 |              1 |
|         85 |              3 |
|        420 |              2 |

see this sqlfiddle

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35573

I think you are seeking a running total.

| USERID |                         DATE |                NAME | RUNNINGSUM |
|--------|------------------------------|---------------------|------------|
|      1 |   May, 10 2014 00:00:00+0000 |            football |          1 |
|      1 |  June, 10 2014 00:00:00+0000 |            football |          2 |
|      1 |  July, 10 2014 00:00:00+0000 |            football |          3 |
|      1 |  July, 10 2014 00:00:00+0000 |            football |          4 |
|      1 |   May, 10 2014 00:00:00+0000 | Machine Bench Press |          5 |
|      1 |  June, 10 2014 00:00:00+0000 | Machine Bench Press |          6 |
|      1 | March, 10 2014 00:00:00+0000 |               salsa |          7 |

MySQL lacks functions like row_number() that make this simple, but here is an approach that achieves the equivalent of row_number() partitioned by userid.

SELECT
       userid
     , date
     , name
     , RunningSum
FROM(
      SELECT  @row_num := IF(@prev_user = ue.userid, @row_num + 1 ,1) AS RunningSum
             , ue.userid
             , ue.date
             , e.name
             , @prev_user := ue.userid
      FROM user_exercises ue
      INNER JOIN exercises e ON ue.exerciseid = e.id
      CROSS JOIN (SELECT @row_num :=1, @prev_user :='') vars
      ORDER BY
               ue.userid
             , ue.date
             , ue.exerciseid
     ) x
ORDER BY
         userid
       , RunningSum

see this sqlfiddle

Upvotes: 0

Dragondark
Dragondark

Reputation: 1

If you want count how many group you have on group by :

$db->query("SELECT e.id as id, e.name, count(id) as total, ue.date
            FROM `users exercises` as ue
            LEFT JOIN `exercises` as e ON exerciseid = e.id
            WHERE ue.`userid` = $userid
            GROUP BY id ASC
            ORDER BY total DESC");

else if you want take previous total for addition, create a procedure like this (I think there are errors in my procedure)

CREATE PROCEDURE name
DECLARE
  record your_table%ROWTYPE;
  nb int DEFAULT 0;
BEGIN

  FOR record IN SELECT e.id as id, e.name as name, count(id) as nbid, ue.date as date
        FROM `users exercises` as ue
        LEFT JOIN `exercises` as e ON exerciseid = e.id
        WHERE ue.`userid` = $userid
        GROUP BY id ASC
        ORDER BY total DESC  
  LOOP
  set nb := nb + record.nbid;
  SELECT record.id,record.name,nb,date;
  END LOOP;
END

regards Dragondark De Lonlindil

Upvotes: 0

Related Questions