How to Create Calculated Field in MySQL

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 the GROUP 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