CODEWITHSUNDEEP
mysqlsqlcount
ditto
ditto

Reputation: 6277

SQL: Counting and grouping rows

id | userid | exerciseid | date                | time | weight | distance | reps
1  | 24     | 1          | 2013-09-28 00:00:00 | 2321 | 231    | 121      | NULL
2  | 24     | 24         | 2013-09-28 00:00:00 | 2321 | 231    | 121      | NULL
3  | 24     | 1          | 2013-09-28 00:00:00 | 2321 | 231    | 121      | NULL
4  | 24     | 1          | 2000-00-00 00:00:00 | NULL | 100    | NULL     | 2
5  | 24     | 1          | 2013-09-28 00:00:00 | 2321 | 231    | 121      | NULL

Rows 1, 3, and 5 are the same. I want to do a count that groups them together, whilst also adding a column with the count value.

SELECT id, userid, exerciseid, date, time, weight, distance, reps    
FROM `exercises`
WHERE `userid` = 1 AND `date` < now()

So I want this to return something similar too:

id | userid | exerciseid | date                | time | weight | distance | reps | count
1  | 24     | 1          | 2013-09-28 00:00:00 | 2321 | 231    | 121      | NULL | 3
4  | 24     | 1          | 2000-00-00 00:00:00 | NULL | 100    | NULL     | 2    | NULL

Upvotes: 0

Views: 88

Answers (2)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try this

SELECT id, userid, exerciseid, date, time, weight, distance, reps,
       count(*) as count
FROM `exercises`
WHERE userid = 1 AND date < now()
GROUP BY id, userid, exerciseid, date, time, weight, distance, reps

Upvotes: 2

bgs
bgs

Reputation: 3213

Try this :

SELECT Count(*) As CountM, id, userid, exerciseid, date, time, weight, distance, reps    
FROM `exercises`
WHERE `userid` = 1 AND `date` < now()
Group by id, userid, exerciseid, date, time, weight, distance, reps

Upvotes: 0

Related Questions