CODEWITHSUNDEEP
mysqlgroup-bysum
Anthony
Anthony

Reputation: 1876

mysql sum multiple groups of data in a single table

Edit: I think I figured out the solution, but I'm still interested to know if anyone has a better way to do it. Adding "GROUP BY name" produced the correct output.

I have a table like this:

Name    StartDate             EndDate
name0   2012-05-18 20:00:00   2012-05-18 20:00:01
name1   2012-05-18 20:00:00   2012-05-18 20:00:05
name0   2012-05-18 20:00:00   2012-05-18 20:00:01
name3   2012-05-18 20:00:00   2012-05-18 20:00:03
name4   2012-05-18 20:00:00   2012-05-18 20:00:07
name3   2012-05-18 20:00:00   2012-05-18 20:00:03
name8   2012-05-18 20:00:00   2012-05-18 20:00:12
name5   2012-05-18 20:00:00   2012-05-18 20:00:11
name1   2012-05-18 20:00:00   2012-05-18 20:00:05

I'm trying to learn how to do two things:

1) List distinct names with the COUNT of their rows and the SUM of their total run time

Name    Count   TotalRunTime
name0   2       2
name1   2       10
name3   2       6
name4   1       7
name5   1       11
name8   1       12

I created the following query to try to do both of these things at once. It works for a single name, but I can't figure out how to make it 'iterate' over the list of distinct names in the table.

SELECT
  Name,
  SUM(TIME_TO_SEC(TIMEDIFF(enddate,startdate))) TotalRunTime,
  COUNT(*) as TotalJobs
FROM results
WHERE name='name0';

Which produces:

+-------+-------------+-----------+
| Name  |TotalRunTime | TotalJobs |
+-------+-------------+-----------+
| name0 |     1023491 |        46 |
+-------+-------------+-----------+
1 row in set (0.00 sec)

But what I want is:

+-------+-------------+-----------+
| Name  |TotalRunTime | TotalJobs |
+-------+-------------+-----------+
| name0 |     1023491 |        46 |
| name1 |      971621 |        35 |
| name3 |      108843 |        22 |
| ...   |         ... |       ... |
+-------+-------------+-----------+
<N> rows in set (0.00 sec)

Upvotes: 0

Views: 228

Answers (1)

aleroot
aleroot

Reputation: 72626

Group BY the name field is the best solution :

SELECT
  Name,
  SUM(TIME_TO_SEC(TIMEDIFF(enddate,startdate))) TotalRunTime,
  COUNT(*) as TotalJobs
FROM results
WHERE name='name0'
GROUP BY Name;

Upvotes: 1

Related Questions