0

Hi i have a table like the following

Time | Prod1 | Prod2 | Prod3 | .....

There are around 150 columns in total.. I have a few questions regarding to this table

1) how can I get a sum of all the rows except the Time row? I was thinking like Select sum(something) from table group by Time; However, I do not know the number of columns or the name of the columns. Therefore, I cant do Select sum(Prod1), sum(Prod2) ... is there any ways I can get a sum of all Prod1, Prod2, Prod3, Prod4..... that is group by Time?

2) I know the table is huge, the table is given it to me like that, is there any ways that I can speed that up? from the first question, there could be over 1000 rows, with 150 columns thats gonna take a long time to calculate, is there any ways that I can speed that up?

kown owl
  • 43
  • 8
  • 1
    I would not recommend this data-style of architecture. If your columns are `Product1`, `Product2`, I think your question should be "How do I normalize my data?" and not "How do I make this select work?" – HoldOffHunger Feb 24 '20 at 15:40
  • @HoldOffHunger if i have to restructure the database, how would you suggest it to be done? basically it is used for tracking the inventory, and there are n amount of products, with 4 different locations. – kown owl Feb 24 '20 at 15:51
  • There are plenty of answers for normalization. Take a look at this one: https://stackoverflow.com/q/1258743/2430549 – HoldOffHunger Feb 24 '20 at 16:04

1 Answers1

0
  1. This script should build the query you need directly from information_schema so it doesn't matter if you don't know the table structure, the only thing you need to know is the table name.
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('SUM(',c.COLUMN_NAME,')') SEPARATOR ', '),'
               FROM TABLE_NAME
               GROUP BY Time')
FROM information_schema.COLUMNS c
WHERE TABLE_NAME='TABLE_NAME'
AND c.COLUMN_NAME<>'Time'

The above script will build a query that sums all the columns in TABLE_NAME except the Time column.

You only need to change TABLE_NAME.

  1. 1000 rows is not a huge table, actually, it's a very small one. MySQL is capable to manage millions or even billions of rows tables, so if you have performance issues with 1000 is basically because of very bad configuration. (You can start with innodb_buffer_pool_size)
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Jesus Uzcanga
  • 276
  • 2
  • 6
  • thank you for the reply, i will give it a try first! – kown owl Feb 24 '20 at 17:06
  • i am getting a result like this `'SELECT SUM(date), SUM(A001), SUM(A002), SUM(A003), SUM(A005), SUM(A006), FROM table GROUP BY Time'` with an extra comma before FROM table, is there any ways to take that out ? – kown owl Feb 25 '20 at 15:35
  • You shouldn't be getting that. We are using GROUP_CONCAT() with SEPERATOR which only includes the separator (,) in-between results. Check and/or post your exact query. Also, post the result of SHOW CREATE TABLE – Jesus Uzcanga Feb 27 '20 at 16:26