Counting repeated values and summing them up by id for million records

Question

I have below problem -

Table with millions of records in below format -

Scale   ID 
01      2001
01      2001
03      2002
05      2003
78      2011
76      2010

so on ..

Scale ranges from 01-100 and ID ranges from 2001 - 2011.

I want a new table out of this which counts each scale for each year.

Output should be something like this.

Scale 2001  2002  2003  2004 ---- 2011
1     2      0     0     0   ----  0
2     0      0     0     0  ------ 0
3     0      1     0     0  ------- 0
4     0      0     0     0  ------ 0
5     0      0     1     0  ------ 0
-
-
-
-  
100

Trying to do it in SQL. any help will be great.

Thanks

The linked duplicate question has answers for both static and dynamic pivoting within mysql. — Shadow, Aug 26 '18 at 01:33

sticky bit · Answer 1 · 2018-08-26T00:48:19.347

1

You can use conditional aggregation.

Group by scale.
In the list of columns use a CASE to check if the ID has the value you want to show in that column. If it has, return any non null value (e.g. 1). Wrap a count() around it. As count() will not count NULLs, and CASE returns NULL as default, when nothing else matched, it will count only the occurrences where ID has the respective value.

Like this:

SELECT `scale`,
       count(CASE `id`
               WHEN 2001 THEN
                 1
             END) `2001`,
       ...
       count(CASE `id`
               WHEN 2011 THEN
                 1
             END) `2011`
       FROM `elbat`
       GROUP BY `scale`
       ORDER BY `scale`;

edited Aug 26 '18 at 00:48

answered Aug 26 '18 at 00:06

sticky bit

36,626
12
31
42

@Earthshaker Keep in mind that `COUNT()` can only work with numeric data.. And will not work on string like 'abc' or datetimes datetypes. – Raymond Nijland Aug 26 '18 at 00:08
@RaymondNijland: What do you mean by that? `count()` accepts any type as parameter, as it only checks for `NULL` or not, it doesn't care if it's a number or a string or whatever. – sticky bit Aug 26 '18 at 00:11
"What do you mean by that?" With `count(CASE id WHEN 2011 THEN 1 END) 2011` then `THEN ` and `THEN ` .. `` needs to be numeric here it can't be a other datatype.. With MAX you don't have that problem. – Raymond Nijland Aug 26 '18 at 00:23
1

@RaymondNijland: No it doesn't. You can also use `'1'` instead of `1` or `'I''m not null!'` or whatever expression, as long as it's not literal `NULL` or evaluates to `NULL`. – sticky bit Aug 26 '18 at 00:26
"No it doesn't" i dont think you get what i mean here check this http://sqlfiddle.com/#!9/d76e50/82 `COUNT(CASE WHEN 1=1 THEN 'a' ELSE 'a' END)` does not give 'a' but 1.. That's why i always advice to use MAX instead because it works on anny datatype which you want to return.. But true you can use '1' as return value because of MySQL autocast. – Raymond Nijland Aug 26 '18 at 00:33
@RaymondNijland: You're right, that `count()` cannot be used to calculate the maximum (not even for numeric types). But the maximum wasn't asked. The OP wants to count ("which counts each scale for each year") not any maximum (if they used your answer, they'd only get `0` or the value of the column header, check the desired result, it doesn't look like that). And I didn't intend to calculate the maximum in my answer either. – sticky bit Aug 26 '18 at 00:33
@RaymondNijland: Has nothing to do with implicit casts: [`SELECT count('I definitely cannot be cast to a number!');`](http://sqlfiddle.com/#!9/9eecb/56595). – sticky bit Aug 26 '18 at 00:36
"Has nothing to do with implicit casts" indeed in that case never use `COUNT()` when you need to return strings. "The OP wants to count" Yes in this case you indeed need to use COUNT or SUM it's late in the morning over here blame it to that – Raymond Nijland Aug 26 '18 at 00:39
Thanks for the help - In above problem lets say of there few other columns like ID & they have similar values like ID has - how can i perform same process for all columns ? I am hoping i don't need to write multiple SQL statements for them. I am looking for some sort of looping solution here. – Earthshaker Sep 04 '18 at 20:25

Nick · Answer 2 · 2018-08-26T01:19:31.757

1

If you're using MySQL you need to do it the hard way...

SELECT scale, 
    SUM(CASE WHEN id=2001 THEN 1 ELSE 0) AS `2001`,
    SUM(CASE WHEN id=2002 THEN 1 ELSE 0) AS `2002`,
    SUM(CASE WHEN id=2003 THEN 1 ELSE 0) AS `2003`,
    ...
    SUM(CASE WHEN id=2011 THEN 1 ELSE 0) AS `2011`
FROM mytable
GROUP BY scale

edited Aug 26 '18 at 01:19

answered Aug 26 '18 at 00:08

Nick

138,499
22
57
95

"If you're using MySQL<8.0 you need to do it the hard way..." Also in MySQL 8.0+ you need to do it a hard way. MySQL 8.0 does not have a native pivot function – Raymond Nijland Aug 26 '18 at 00:21
Thanks @RaymondNijland have updated the answer. – Nick Aug 26 '18 at 01:20
Thanks for the help - In above problem lets say of there few other columns like ID & they have similar values like ID has - how can i perform same process for all columns ? I am hoping i don't need to write multiple SQL statements for them. I am looking for some sort of looping solution here. – Earthshaker Sep 04 '18 at 20:25
@Earthshaker Probably yes. I suspect you will need a stored procedure to do the looping (rather than writing all those queries by hand). It's a big enough topic for another question. – Nick Sep 04 '18 at 23:17

Counting repeated values and summing them up by id for million records

2 Answers2