Database calculation

[Tasos]

Hello,
 
I need some help and information about how to calculate.
 
is it possible to make a calculation directly from database and output ? here below an example.
 
database : Expense

table: Money

----------------

|      Gas       |        Gasoline      |      Diesel     |           total          |
 ...................................................................................................
       $50                  $20                    $10         =        $80      

 

So what i want is to make the calculation like here above directly from database like today whe used

 

 ...................................................................................................
       $10                  $5                   $10         =        $25

 

Thank in advance/ 

 

 

[mac_gyver]

you can SELECT just about anything you want in a query.

[requinix]

You can do it just fine in the code, you know.

SELECT Gas + Gasoline + Diesel AS Total FROM Money

Also looks like your table isn't normalized...

[vinny42]

 

Also looks like your table isn't normalized.

 

Possibly, repeating columns of the same type are usually not good, unles the options have extremely little chance of chaning, like fueltypes :-)

[requinix]

Possibly, repeating columns of the same type are usually not good, unles the options have extremely little chance of chaning, like fueltypes :-)

Hydrogen. Electric. Biodiesel.

 

Just because you can't think of more doesn't mean they won't happen.

Edited September 10, 2013 by requinix

[vinny42]

 

Just because you can't think of more doesn't mean they won't happen. 

 

That alone is not enough. Normalization is done (amung other things) to minimize the amount of impact of adding a new fuel. But, a normalized table can be more much more complex/inefficient to query, depening on your application (materialized views are nearly always de-normalized representations of normalized data, used for faster access to the data)

 

So, if new fuels arrive once every five years  (it took 100 years to get to the six mentioned here) and your de-normalized table outperforms the normalized version where it matters most then by all means go for the denormalized one.

[Tasos]

SELECT Gas + Gasoline + Diesel AS Total FROM Money

Thanks for that, i know and i read about i on how to do that with code.

 

But i want to know and to learn more about databases so i would like to do that direct from database if possible can 

database do something like that without writing any code ?

 

Thanks.

Edited September 10, 2013 by Tasos

[vinny42]

 

But i want to know and to learn more about databases so i would like to do that direct from database if possible can 

database do something like that without writing any code ?

 

Yould make the database calculate the total using a trigger and store it in a separate column, or you could create a view that will calculate the value and present the result as a virtual table.

 

But what are you trying to accomplish exactly? (different goals require different approaches)

[Tasos]

 

 

But what are you trying to accomplish exactly? (different goals require different approaches)

 

 

[vinny42]

Yeah, you want the total, I got that :-) but where, what, how, why. You say you don't want to write any code, why is that, are you stuck with accessing a table directly through some tool that doesn't support basic SQL and doesn't have the ability to add columns itself?

[requinix]

That alone is not enough. Normalization is done (amung other things) to minimize the amount of impact of adding a new fuel. But, a normalized table can be more much more complex/inefficient to query, depening on your application (materialized views are nearly always de-normalized representations of normalized data, used for faster access to the data)

 

So, if new fuels arrive once every five years  (it took 100 years to get to the six mentioned here) and your de-normalized table outperforms the normalized version where it matters most then by all means go for the denormalized one.

And when that happens you'll have to add the new fuel type to any place that references them, test the changes, fix resulting bugs, test again, and deploy the new code.

 

Performance alone is not a good enough reason to denormalize.

[Tasos]

I have a script that already build automatic tables without writing a single code. So what i want to know is how to make a calculation through database like

for example if you select in database TEXT database know it is just text or TINYINT database knows it is NUMERIC...

 

So what i am asking if it is possible to select something through database and make the calculation.

 

If you see at the picture the mouse pointer is on varchar so i want to select from type something and to make the calculation is that possible ?

 

 

 

Sorry for my explanation..

 

Again THANKS FOR THE REPLYS !!!

Edited September 10, 2013 by Tasos

[Tasos]

I figure it out it is not possible to make a calculation without to write code so damn i am 2 days searching for something like that ......

 

Thank for your help requinix and vinny42.

[Psycho]

I figure it out it is not possible to make a calculation without to write code so damn i am 2 days searching for something like that ......

 

Thank for your help requinix and vinny42.

 

Yes it is possible - just not how you are thinking to do it.

[vinny42]

If the tables are generated by some tool then the tool would have to add the triggers or add the view to create the total.

 

I suppose you could write a stored procedure that can create the trigger from the table's design but I'm not sure if that's the way to go.

 

 

 

And when that happens you'll have to add the new fuel type to any place that references them, test the changes, fix resulting bugs, test again, and deploy the new code.

 

That's what I said; adding a fuel type to a denormalized design will take longer than adding to a normalized design. (how long depends on how clever you are about the implementation) 

 

The question is: which is better for the current situation? And that question is often not asked, people just normalize because that's what they've been told to do when something variable needs to be stored, even if it only varies once in a lifetime.

[Psycho]

First off, what you are wanting to do is a poor implementation. There is no good reason to create a field that automatically holds the sum of the other fields when you can just as easily write your query to get that data for you.

 

SELECT Gas, Gasoline, Diesel, (Gas + Gasoline + Diesel) as total
FROM table_name

 

Yes, you can create a trigger to sum the values and populate another field, but it opens up the possibility of getting the sum and the values out of sync if you don't cover all your bases (all the different INSERT & UPDATE scenarios). Dynamically generating the sum is the best way to go for something like this as it guarantees to get you the right value each and every time. Databases are made to do these types of calculations and do so very efficiently.

 

But, if you really want to go with the other approach, then just create a trigger. Create a new field called 'total' using the same field type that you used for the other fields. Then run this query in PHPMyAdmin to create a trigger.

 

CREATE TRIGGER ins_sum BEFORE INSERT ON TABLE_NAME
FOR EACH ROW SET NEW.Total = NEW.Gas + NEW.Gasoline + NEW.Diesel

 

Now, whenever a new record is added, the value for "Total" will be auto-populated with the sum of the other three fields.

[vinny42]

 

Yes, you can create a trigger to sum the values and populate another field, but it opens up the possibility of getting the sum and the values out of sync if you don't cover all your bases (all the different INSERT & UPDATE scenarios).

 

 

...and you cannot make  a mistake in a select query?

 

 

Dynamically generating the sum is the best way to go for something like this as it guarantees to get you the right value each and every time.

 

It guarantees that you will get what you ask for. Whether that is the correct total depends on whether your select query is correct. If the database get's a new column your select query will never now about it unless *you* modify it.

This may be of trivial concern to you as a single developer but if this table is in a big system you don't want to have your users decide what the "total" is, or indeed keep track of whether the cancluation for the total has changed.

 

 

Databases are made to do these types of calculations and do so very efficiently.

 

And yet you can't sort on a dynamically calculated total if your table has a decent number of records in it.

(plus you don't want to waste time re-calculating the totals every time, watch the read-write ratios on the database to decide)

 

I agree that you should let the database do what it's good at, and these materialized columns are on of the things it is good at.

[Psycho]

...and you cannot make  a mistake in a select query?

 

 . . .

 

It guarantees that you will get what you ask for. Whether that is the correct total depends on whether your select query is correct. If the database get's a new column your select query will never now about it unless *you* modify it.

This may be of trivial concern to you as a single developer but if this table is in a big system you don't want to have your users decide what the "total" is, or indeed keep track of whether the cancluation for the total has changed.

 

 

 

And yet you can't sort on a dynamically calculated total if your table has a decent number of records in it.

(plus you don't want to waste time re-calculating the totals every time, watch the read-write ratios on the database to decide)

 

I agree that you should let the database do what it's good at, and these materialized columns are on of the things it is good at.

 

Well, yeah, the query will return what you ask it return. If you ask for the wrong thing then, obviously, you will get the wrong results. All of the "reasons" for this being a bad idea are either invalid and/or are reasons why the data should have normalized your data to begin with.

 

If the database get's a new column your select query will never now about it unless *you* modify it

If the data was normalized then the query would not have to change when new fuel types are added. It would just JOIN out to the dependent table and do a SUM() using GROUP BY.

 

This may be of trivial concern to you as a single developer but if this table is in a big system you don't want to have your users decide what the "total" is, or indeed keep track of whether the cancluation for the total has changed.

Again, a normalized database with an appropriate query would solve this. Even without a normalized database, if this was to be used for a "big" system with multiple developers then it should use a Data Abstraction Layer - the developers should not be writing their own queries. They should instead use a library of predefined methods for database activities. Then when a change in the DB occurs only those methods would need to be changed and not any of the business logic code.

 

And yet you can't sort on a dynamically calculated total if your table has a decent number of records in it.

Considering the OP is apparently planning to output the data based upon dates I don't see how that is relevant.

[vinny42]

This is turning into a long discussion but still educational I think, so please don't think of me as a pain in the backside (although I am), I'm trying to make sure that you understand what I'm saying and why.

All due respect, no offence etc. etc.

 

All of the "reasons" for this being a bad idea are either invalid and/or are reasons why the data should have normalized your data to begin with.

 

Again, normalization and realtime selecting can be the way to go, but only if you can work with the consequences. Just like denormalization and materialized columns can be the way to go if you need their advantages.

 

If the data was normalized then the query would not have to change when new fuel types are added. It would just JOIN out to the dependent table and do a SUM() using GROUP BY.

Again, a normalized database with an appropriate query would solve this. 

 

If the number of records is small enough to allow that, then yes, obviously. But not when the table has millions of rows, or if it needs to be queried a hundred times a second.

 

the developers should not be writing their own queries.

 

The who's going to write the abstraction code? :-)

 

Developers must write queries, it's their job. It's up to the DBA to make sure they can't do silly things like run a sum with a group-by on a table with ten million rows.

One of the ways a DBA can do that is by implementing the totals column, in whatever way is most reliable and most performant; he could use a trigger, he could define a view

and redirect the developers to the view instead.

 

 

Considering the OP is apparently planning to output the data based upon dates I don't see how that is relevant.

 

We don't know anything about what he's trying to do or is working on, so I like to tell people about the consequences of default solutions like "you should normalize" and "you must always select in realtime". If he says it's only going to be a single-user database that holds a thousand records with manual updates once every week, then it really doesn't matter what he does, it's all going to be equally fast and just as much work to maintain.