how to create calculated field in mysql?
Per MySQL docs (emphasis added) you cannot have an expression in a default value:
10.1.4. Data Type Default Values
The DEFAULT value clause in a data
type specification indicates a default
value for a column. With one
exception, the default value must be a
constant; it cannot be a function or
an expression. This means, for
example, that you cannot set the
default for a date column to be the
value of a function such as NOW() or
CURRENT_DATE. The exception is that
you can specify CURRENT_TIMESTAMP as
the default for a TIMESTAMP column.
I've tested that the following trigger works for your intent:
CREATE TRIGGER MyTriggerName
BEFORE INSERT ON info
FOR EACH ROW
SET NEW.NewField = md5(NEW.username);
How to create a conditional calculated column in SQL?
You may be able to use a case statement in the calculation.
SELECT AIR.Code, AIR.City, AIR.Country, SUM(CASE WHEN flight.f = 'C' then £
when flight.f = 'A' then (-1) * £
else null
end) as "£ calculated"
from AirportTable as "air"
join FlightTable as "flight" on "air".CODE = "flight".code -- could be AKEY = AKEY
How to add calculated column with LAG in SQL?
Calculated columns (aka computed columns, aka generated columns) in a TABLE
(as in CREATE TABLE
or ALTER TABLE
) cannot contain queries, they can only be expressions derived from other columns in the same row.
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
- Values of a generated column are computed from an expression included in the column definition.
- Generated column expressions must adhere to the following rules
- [...]
- Subqueries are not permitted.
Instead, you can do this using a VIEW
. Your application code or reports would then query the view (prices_with_delta
), not the base table (prices
):
CREATE VIEW sandbox.prices_with_delta AS
SELECT
p2.*,
COALESCE( LN( p2.price / p2.prior ) ) AS ln_change
FROM
(
SELECT
p.*,
LAG( p.price, 1 ) OVER( PARTITION BY p.ticker ORDER BY p.date ) AS prior
FROM
sandbox.prices AS p
) AS p2
reuse a calculated field in the same query
Column aliases cannot be re-used in the SELECT
where they are defined -- and for a simple reason. MySQL (in particular) and SQL in general does not guarantee the order of evaluation of expressions in the SELECT
.
In your case, the simplest solution is to repeat the expression, because it is so simple.
You have another problem in your query, though. You are aggregating by banca
but only selecting nome
.
Here is a better way to write the query:
SELECT c.nome, sum(?.amount) AS total,
(1500 - sum(?.amount)) AS residuo
FROM movimenti_carta mc JOIN
carte c
ON mc.banca = c.id
WHERE ?.data >= '2019-05-01' AND
?.data < '2019-06-01'
GROUP by c.nome;
Note the changes:
- All column references should be qualified. The
?
is for the alias for the table where the column comes from. - Use table aliases, which are abbreviations of the table names.
- The unaggregated columns in the
SELECT
are in theGROUP BY
. - The date arithmetic works for both dates and date/time values.
mySQL: Can I add a calculated column with the sum of all other rows that have that same shared value
Use window functions:
select city, zipcode, sum(population) as population,
sum(population) / sum(sum(population)) over (partition by city) as zipcode_ratio
from t
group by city, zipcode;
Related Topics
Invalid Column Name on SQL Server Update After Column Create
For Autoincrement Fields: Max(Id) VS Top 1 Id Order by Id Desc
Transact-SQL Shorthand Join Syntax
SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword
Laravel Eloquent to Join Table and Count Related
With Hibernate, How to Query a Table and Return a Hashmap with Key Value Pair Id>Name
SQL Server, Converting Seconds to Minutes, Hours, Days
Number of Fridays Between Two Dates
SQL Server 2008:Cannot Insert New Column in the Middle Position and Change Data Type
How to Calculate a Moving Average Using MySQL
Extract Phone Number from Noised String
Concatenate One Field After Group By
How to Generate All Possible Data Combinations in SQL
Psycopg2 Equivalent of MySQLdb.Escape_String
Oracle Table Column Name with Space
Generate a Unique Column Sequence Value Based on a Query Handling Concurrency