2

Trying to figure out best way to sum large amount of data. I found this, but the code needs to be automated for large amount of data. I'm thinking could this be done with pivot tables?

The data is looking like this:

Type  | 1.1. 0:00  | 1.1. 1:00
x1    | 2          | 1
x1    | 1          | 2
x2    | 3          | 1
x2    | 1          | 1
x3    | 2          | 3

Result would look like this:

Type  | 1.1. 0:00  | 1.1. 1:00
x1    | 3          | 3
x2    | 4          | 2
x3    | 2          | 3
Worthwelle
  • 1,244
  • 1
  • 16
  • 19
Mr. Man
  • 21
  • 1
  • This looks like a good candidate for Pivot tables. Did you face any issues using Pivot Tables – Nivas Mar 04 '19 at 20:21
  • Well I tried it but since there is something like 1000 timestamps and 1000 type stamps it's nearly impossible to do effectively. I guess using python is best shot – Mr. Man Mar 05 '19 at 20:29

2 Answers2

1

You seem to be looking for a simple aggregate query:

SELECT type, SUM(col1) AS col1, SUM(col2) AS col2 FROM mytable GROUP BY type

NB: 1.1. 0:00 and 1.1. 1:00 are quite unusual column names ; you probably want to rename them to avoid the need to escape them whenever you need to access them. I renamed them to col1 and col2.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Well they are time stamps, so I think it's necessary. Can this be done automated... like: SELECT type, SUM(col1) AS col1, SUM(col2) AS col2..... ..... SUM(col300) AS col 300 FROM mytable GROUP BY type – Mr. Man Mar 04 '19 at 19:37
  • @Mr.Man: to me these are columns headings, so you should be free to change them. But anyway, that does not change the solution... – GMB Mar 04 '19 at 19:48
  • @Mr.Man: no, a SQL query cannot dynamically generate the 1..300 columns for you. You would need to use dynamic SQL, which is more complicated. However, many other tools can help generating such a string with repeating parts, eg Excel formulas can easily do that for you. – GMB Mar 04 '19 at 19:49
0

I was going to ask a few clarifying questions before the formatting of your data kicked in.

I concur with GMB.

wolfsshield
  • 757
  • 5
  • 14