Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
MSQL in 10 mn ~ Lesson 9 Performing Mathematical Calculations - orderi...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
-- https://www.safaribooksonline.com/library/view/sams-teach-yourself/9780134649160/ch09.html -- Lesson 9 Performing Mathematical Calculations - orderitems sample #1 CREATE TABLE orderitems ( order_num INT NOT NULL , order_item INT NOT NULL , prod_id NCHAR(10) NOT NULL , quantity INT NOT NULL , item_price MONEY NOT NULL ); ALTER TABLE orderitems ADD CONSTRAINT pk_orderitems PRIMARY KEY (order_num, order_item); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (20005, 1, 'ANV01', 10, 5.99) ,(20005, 2, 'ANV02', 3, 9.99) ,(20005, 3, 'TNT2', 5, 10) ,(20005, 4, 'FB', 1, 10) ,(20006, 1, 'JP2000', 1, 55) ,(20007, 1, 'TNT2', 100, 10) ,(20008, 1, 'FC', 50, 2.50) ,(20009, 1, 'FB', 1, 10) ,(20009, 2, 'OL1', 1, 8.99) ,(20009, 3, 'SLING', 1, 4.49) ,(20009, 4, 'ANV03', 1, 14.99); /* Performing Mathematical Calculations Another frequent use for calculated fields is performing mathematical calculations on retrieved data. Let’s take a look at an example. The item_price column contains the per-unit price for each item in an order. To expand the item price (item price multiplied by quantity ordered), you simply do the following: */ SELECT prod_id , quantity , item_price , quantity*item_price AS expanded_price FROM orderitems WHERE 1=1 AND order_num = 20005 ;
View schema
Execution time: 0,02 sec, rows selected: 4, rows affected: 11, absolute service time: 0,17 sec
edit mode
|
history
|
discussion
prod_id
quantity
item_price
expanded_price
1
ANV01
10
5,9900
59,9000
2
ANV02
3
9,9900
29,9700
3
TNT2
5
10,0000
50,0000
4
FB
1
10,0000
10,0000