CODEWITHSUNDEEP
sqlpostgresql
Ryan
Ryan

Reputation: 1

Finding the variance across columns for a single row in postgreSQL

My database is set up like this

CustomerNumber  Bill_Apr   Bill_Mar    Bill_Feb    Bill_Jan

I need to create a new column that has the variance across the monthly bills.

If I can't do this, will I need to restructure the database to have multiple customer numbers with each month's bill as a different row? I would much rather set up to where I can calculate across columns.

Upvotes: 0

Views: 1412

Answers (1)

Michael
Michael

Reputation: 1596

I would recommend you restructure the table to be 1 row per customer per month. With your current structure, what happens next year with January? Do you overwrite the value? What happens next month where you don't have a column? Do you add a column and then update your calculated column?

I would recommend the following structure:

CREATE TABLE Bills
(CustomerNumber int,
MonthYear varchar(30),
Bill double,
)

Then, you could write a view or query that would look like

SELECT CustomerNumber, VARIANCE(Bill) as BillVariance
FROM Bills
GROUP BY CustomerNumber

This will return the statistical variance for all of the bills by each customer.

Upvotes: 1

Related Questions