CODEWITHSUNDEEP
mysqlsqlsumaccumulate
Jago
Jago

Reputation: 2935

MySQL: Accumulate occurrences of each specific value in a field

I want to get a field that contains the # number of occurrence of a value in a field.

CREATE TABLE `events` (
`id` INT NOT NULL AUTO_INCREMENT,
`country` VARCHAR(2) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO `events` (`country`) VALUES
('es'), ('fr'), ('uk'),
('uk'), ('es'), ('uk'),
('fr'), ('it'), ('es'),
('es'), ('it'), ('uk'),
('fr'), ('es'), ('de')

That is, given this SQLFiddle, I want to add a field accumulated to the results, containing the number of times that a value in field country has appeared until that row.

SELECT e.*,
(SELECT COUNT(*) FROM `events` AS e2
 WHERE e2.country = e.country) AS times
FROM `events` AS e

ID  | COUNTRY | TIMES | ACCUMULATED
----+---------+-------+------------
1   | es      | 5     | 1
2   | fr      | 3     | 1
3   | uk      | 4     | 1
4   | uk      | 4     | 2
5   | es      | 5     | 2
6   | uk      | 4     | 3
7   | fr      | 3     | 2
8   | it      | 2     | 1
9   | es      | 5     | 3
10  | es      | 5     | 4
11  | it      | 2     | 2
12  | uk      | 4     | 4
13  | fr      | 3     | 3
14  | es      | 5     | 5
15  | de      | 1     | 1

I guess that is some kind of conditional SUM, but I am not sure how.

Upvotes: 1

Views: 225

Answers (1)

semao
semao

Reputation: 1757

No sum is necessary. I think you just need second condition "until that row":

select e.*,
       (select count(*) from `events` as e2
        where e2.country = e.country
       ) as times,
       (select count(*) from `events` as e3
        where e3.country = e.country AND e3.id <= e.id
       ) as accumulated
from `events` as e

http://sqlfiddle.com/#!2/1f6bb2/44

Upvotes: 2

Related Questions