SQL sum one value for each unique value from another column

Question

If I have a table like this:

------------------------------
|id    |duration |date       |
|1     |20       |1392479451 |<--
|2     |20       |1392479451 |
|3     |10       |1392478620 |<--
|4     |30       |1392479457 |<--
|5     |30       |1392479457 |
|6     |30       |1392479457 |
------------------------------

I only want to SUM the first of each unique date:

----------------
|SUM(duration) |
|60            |
----------------

How do I do this, please?

you need to use `group by date` at your query – Shaiful Islam Jan 21 '15 at 20:48 — Shaiful Islam, Jan 21 '15 at 20:48

shmosel · Accepted Answer · 2015-01-21T20:39:16.030

4

This should do it:

SELECT SUM(duration) FROM (
    SELECT duration FROM tbl GROUP BY date
) t;

The GROUP BY in the inner query will select only one row per distinct date. Which row will be selected is not defined, but it doesn't matter as the durations should be identical. Then we just sum the durations.

EDIT: Just to be clear, this will not select the first duration, but any single duration for a given date. If the durations can vary within a date and you specifically need the first one, this will not solve your problem.

edited Jan 21 '15 at 20:39

answered Jan 21 '15 at 20:17

shmosel

49,289
6
73
138

This uses a MySQL extension that the documentation explicitly warns against using. In the subquery, there is no guarantee that `duration` is the "first" record for the `date`. – Gordon Linoff Jan 21 '15 at 20:22
@GordonLinoff I think I was clear on that point. My understanding is that `duration` doesn't vary within a given date. – shmosel Jan 21 '15 at 20:24
. . Nothing in the question suggests that `duration` needs to be unique for each date. If so, you might as well `group by date, duration` and avoid the need for non-standard SQL. – Gordon Linoff Jan 21 '15 at 20:40
@GordonLinoff The sample data suggests it. As far as the use of non-standard SQL, I would say it's ultimately a matter of preference. Personally, I'm comfortable using it, and I prefer the shorter `GROUP BY` syntax. – shmosel Jan 21 '15 at 20:45

potashin · Answer 2 · 2015-01-21T20:21:45.163

0

You can try something like this:

SELECT SUM(IF(`date` = ( SELECT MIN(`date`) FROM table WHERE `date` = t.`date`), `duration`, 0))    
FROM `table` t

Or

SELECT SUM(t1.`duration`) 
FROM `table` t1 
JOIN ( SELECT MIN(`id`) AS `id` FROM `table` GROUP BY `date` ) t2 ON t1.`id` = t2.`id`

edited Jan 21 '15 at 20:21

answered Jan 21 '15 at 20:15

potashin

44,205
11
83
107

score 0 · Answer 3 · answered Jan 21 '15 at 20:20

You need to get the first date. There are several ways, but because MySQL supports silent conversion of strings to numbers, you can use the substring_index()/group_concat() trick -- so long as the values on each date do not cause the intermediate string to overflow (you would need a lot of values).

The following should do what you want:

select sum(duration + 0)
from (select substring_index(group_concat(duration order by id), ',', 1) as duration
      from table t
      group by date
     ) t;

Otherwise, you need an additional join:

select sum(duration)
from table t join
     (select min(id) as id
      from table t
      group by date
     ) tt
     on t.id = tt.id;

score 0 · Answer 4 · answered Jan 21 '15 at 20:22

0

You can try this:

SELECT 
    SUM(duration)
FROM
    datetable d,
    (SELECT 
        date, MIN(id) AS id
    FROM
        datetable
    GROUP BY date) d2
WHERE
    d.id = d2.id;

answered Jan 21 '15 at 20:22

JuanN

698
1
13
21

score 0 · Answer 5 · edited May 23 '17 at 11:50

0

Per Trying to sum distinct values SQL:

SELECT SUM(duration) FROM 
   (SELECT DISTINCT date, duration FROM table)

edited May 23 '17 at 11:50

Community

1
1

answered Jan 21 '15 at 20:22

Ari Roth

5,392
2
31
46

SQL sum one value for each unique value from another column

5 Answers5