Calculated fields can be essential for a fully functioning finance database. Here we will take a look at one or two basic scenarios to introduce the calculated fields concept. First let’s consider a simple database that finds the difference between two of its fields. We can initiate such a computation from the CREATE CODE section.

CREATE TABLE TestDiff ( Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, Val1 INT(11) NOT NULL, Val2 INT(11) NOT NULL, Total INT(11) DEFAULT NULL, PRIMARY KEY (Id) );

CREATE TABLE sales ( name VARCHAR(20), price_eur DOUBLE, amount INT, total_eur DOUBLE AS (price_eur * amount), total_usd DOUBLE AS (total_eur * xrate), xrate DOUBLE);

Now let's take a look at another mysql calculated fields example, using some different functions.

Using FLOAT or DOUBLE, basic points

FLOAT and DOUBLE, both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column. FLOAT is accurate to approximately 7 decimal places, and DOUBLE upto 14. Decimal’s declaration and functioning is similar to Double. But there is one big difference between floating point values and decimal (numeric) values. We use DECIMAL data type to store exact numeric values, where we do not want precision but exact and accurate values. A Decimal type can store a Maximum of 65 Digits, with 30 digits after decimal point. Let's take a slightly more detailed look, and give a simple example as illustration. Create 2 Columns with Types Double and Decimal and Store value 1.95 in both of them. If you print each column as Integer then you will see than Double Column has printed 1, while Decimal column printed 2.

nOW LET'S JUST TAKE A MORE DETAILED LOOK AT SELECT STATEMENT- The SELECT statment is used to select data from a table. The tabular result is stored in a result table(called a result-set) SELECT column-name(s) FROM table-name e.g. To select columns "LastName" and "FirstName" from table PERSONS SELECT LastName, FirstName FROM Persons THE SELECT DISTINCT STATEMENT The DISTINCT keyword is used to return only distinct (different) values E.G. To select all values from column named "Company" SELECT Company FROM Orders gives Company Orders RR B12 UU B12 Walls A56 UU C32 Now using SELECT DISTINCT - SELECT DISTINCT Company FROM Orders Company RR UU Walls

More to follow on mysql statements

©penninescripts