MySQL Axis table
| |
15 July 09:19
axis table or a crosstab report
SQL Appropriate Functions: Do it after if, case, or GROUP_CONCAT.
Yes, there is use for this...if statements sometimes couldcause problems
if acclimated in combination.
The simple secret, and its aswell why they plan in about all databases, is the
afterward functions:
o assurance (x) allotment -1,0, +1 for ethics x < 0, x = 0, x > 0 respectively
o abs( sign( x) ) allotment 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) accompaniment of the above, back this allotment 1 alone if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0
Data for abounding example:
Make TABLE exams (
pkey int(11) NOT Absent auto_increment,
name varchar(15),
assay int,
account int,
PRIMARY KEY (pkey)
);
admit into exams (name,exam,score) ethics (Bob,1,75);
admit into exams (name,exam,score) ethics (Bob,2,77);
admit into exams (name,exam,score) ethics (Bob,3,78);
admit into exams (name,exam,score) ethics (Bob,4,80);
admit into exams (name,exam,score) ethics (Sue,1,90);
admit into exams (name,exam,score) ethics (Sue,2,97);
admit into exams (name,exam,score) ethics (Sue,3,98);
admit into exams (name,exam,score) ethics (Sue,4,99);
mysql> baddest +------+------+------+-------+
| pkey | name | assay | account |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> baddest name,
sum(score sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the aloft axis table was created with one baddest account
You may anticipate IFs would be apple-pie but WATCH OUT!
Attending what the afterward gives (INCORRECT !!):
mysql> baddest name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams accumulation by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | Absent | Absent | Absent |
| Sue | 90 | Absent | Absent | Absent |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> baddest name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
Aloft delta_1_2 shows the aberration amid the first and additional exams, with the numbers
getting absolute because both Bob and Sue bigger their account with anniversary exam. Artful
the deltas actuality shows its accessible to analyze two rows, not columns which is calmly done
with the accepted SQL statements but rows in the aboriginal table.
mysql>select name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score
sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)
TotalIncPoints shows the sum of the deltas.
baddest name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score
sum(score sum(score sum(score
(sum(score sum(score sum(score sum(score
from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)
Its accessible to amalgamate Absolute Accretion Point TotalIncPoints with AVG. In fact, its accessible to amalgamate
all of the archetype cuts of the data into one SQL statement, which provides added options for displaying
data on your page
baddest name,
sum(score sum(score sum(score sum(score
(sum(score sum(score
(sum(score sum(score
(sum(score sum(score
(sum(score sum(score sum(score sum(score
from exams accumulation by name;
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)
Assay array are advertisement forth with affective averages...again its all with one
baddest statement.
Good commodity on Cantankerous tabulations or de-normalizing data to appearance stats:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
SQL Appropriate Functions: Do it after if, case, or GROUP_CONCAT.
Yes, there is use for this...if statements sometimes couldcause problems
if acclimated in combination.
The simple secret, and its aswell why they plan in about all databases, is the
afterward functions:
o assurance (x) allotment -1,0, +1 for ethics x < 0, x = 0, x > 0 respectively
o abs( sign( x) ) allotment 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) accompaniment of the above, back this allotment 1 alone if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0
Data for abounding example:
Make TABLE exams (
pkey int(11) NOT Absent auto_increment,
name varchar(15),
assay int,
account int,
PRIMARY KEY (pkey)
);
admit into exams (name,exam,score) ethics (Bob,1,75);
admit into exams (name,exam,score) ethics (Bob,2,77);
admit into exams (name,exam,score) ethics (Bob,3,78);
admit into exams (name,exam,score) ethics (Bob,4,80);
admit into exams (name,exam,score) ethics (Sue,1,90);
admit into exams (name,exam,score) ethics (Sue,2,97);
admit into exams (name,exam,score) ethics (Sue,3,98);
admit into exams (name,exam,score) ethics (Sue,4,99);
mysql> baddest +------+------+------+-------+
| pkey | name | assay | account |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> baddest name,
sum(score sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the aloft axis table was created with one baddest account
You may anticipate IFs would be apple-pie but WATCH OUT!
Attending what the afterward gives (INCORRECT !!):
mysql> baddest name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams accumulation by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | Absent | Absent | Absent |
| Sue | 90 | Absent | Absent | Absent |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> baddest name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
Aloft delta_1_2 shows the aberration amid the first and additional exams, with the numbers
getting absolute because both Bob and Sue bigger their account with anniversary exam. Artful
the deltas actuality shows its accessible to analyze two rows, not columns which is calmly done
with the accepted SQL statements but rows in the aboriginal table.
mysql>select name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score
sum(score sum(score sum(score from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)
TotalIncPoints shows the sum of the deltas.
baddest name,
sum(score sum(score sum(score sum(score sum(score sum(score sum(score
sum(score sum(score sum(score
(sum(score sum(score sum(score sum(score
from exams accumulation by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)
Its accessible to amalgamate Absolute Accretion Point TotalIncPoints with AVG. In fact, its accessible to amalgamate
all of the archetype cuts of the data into one SQL statement, which provides added options for displaying
data on your page
baddest name,
sum(score sum(score sum(score sum(score
(sum(score sum(score
(sum(score sum(score
(sum(score sum(score
(sum(score sum(score sum(score sum(score
from exams accumulation by name;
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)
Assay array are advertisement forth with affective averages...again its all with one
baddest statement.
Good commodity on Cantankerous tabulations or de-normalizing data to appearance stats:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
|
score, exams, delta, values, select, mysql, insert, exam3, exam4, exam1, exam2, group, table, pivot, totalincpoints, shows, returns, example, statement, , sum score, score sum, exam score, name exam, score values, insert into, exams name, exams group, select name, name sum, score from, mysql select, score null, values sue, abs sign, values bob, pivot table, name exam score, exams name exam, exam score values, insert into exams, sum score from, score from exams, mysql select name, one select statement, mysql pivot table, sec mysql select, |
Share MySQL Axis table:
Text link code :
Hyper link code:
Also see ...
Ada Programming Libraries Web AWS
__TOC__AWS is a complete framework to advance Web application. The capital allotment of the framework is the anchored Web server. This baby yet able Web server can be anchored into your appliance so your appliance will be able to allocution with a accepted Web browser like Internet Explorer
__TOC__AWS is a complete framework to advance Web application. The capital allotment of the framework is the anchored Web server. This baby yet able Web server can be anchored into your appliance so your appliance will be able to allocution with a accepted Web browser like Internet Explorer
Ada Programming Ada 2005
__TOC__/noincludeThis is an overview of the above appearance that will be accessible in the accessible Ada 2005 (sometimes refered as Ada 200Y or Ada 2006). For the account and a added abundant (and actual technical) description, see the abstract of the to the Ada Advertence Chiral afterwa
__TOC__/noincludeThis is an overview of the above appearance that will be accessible in the accessible Ada 2005 (sometimes refered as Ada 200Y or Ada 2006). For the account and a added abundant (and actual technical) description, see the abstract of the to the Ada Advertence Chiral afterwa
Ada Programming Bales
__TOC__/noincludeOne of the better advantages of Ada over alotof additional programming languages is its able bodied authentic arrangement of modularization and separate compilation. Even admitting Ada allows separate compilation, it maintains the able blazon blockage apartof the assorted
__TOC__/noincludeOne of the better advantages of Ada over alotof additional programming languages is its able bodied authentic arrangement of modularization and separate compilation. Even admitting Ada allows separate compilation, it maintains the able blazon blockage apartof the assorted
Compiler Architecture Case abstraction 1
This is a affiliate from PrefaceThe purpose of this case abstraction is to use simple interpretersto accommodate a affable addition to some accumulation techniques,and to acquaint a few added computer science concepts.Interpreters are acclimated rather than compilers back theybr
This is a affiliate from PrefaceThe purpose of this case abstraction is to use simple interpretersto accommodate a affable addition to some accumulation techniques,and to acquaint a few added computer science concepts.Interpreters are acclimated rather than compilers back theybr
C++ Programming Accompanying Or Agnate Languages
There isnt a absolute language. It all depends on the accoutrement and the objective. The optimal accent (in agreement of run time performance) is apparatus cipher but no one can allege continued curve of binary... the complication of autograph ample systems is astronomic with high level languag
There isnt a absolute language. It all depends on the accoutrement and the objective. The optimal accent (in agreement of run time performance) is apparatus cipher but no one can allege continued curve of binary... the complication of autograph ample systems is astronomic with high level languag
Ada Programming Belvedere Windows
__TOC__/noincludeThe afterward Libraries and Accoutrement advice you if you ambition the MS Windows Platform.; : Win32 binding; [http://www.rrsoftware.com/html/prodinf/claw/claw.htm CLAW] : Addition Win32 bounden that works with any Ada 95 compiler. An anterior copy is accessible
__TOC__/noincludeThe afterward Libraries and Accoutrement advice you if you ambition the MS Windows Platform.; : Win32 binding; [http://www.rrsoftware.com/html/prodinf/claw/claw.htm CLAW] : Addition Win32 bounden that works with any Ada 95 compiler. An anterior copy is accessible
Computer programming Circumstantial programming
__TOC__Concurrent programming is a address that provides for the beheading of operations accordingly either aural a individual computer, or beyond a amount of systems. In the closing case, the appellation is used. machines accomplish bigger achievement by demography advantage of this a
__TOC__Concurrent programming is a address that provides for the beheading of operations accordingly either aural a individual computer, or beyond a amount of systems. In the closing case, the appellation is used. machines accomplish bigger achievement by demography advantage of this a
Computer programming Acute programming
__TOC__In , acute programming, as against to , is a that describes ciphering in agreement of a and statements that change the program state. In abundant the aforementioned way as the in s expresses commands to yield action, acute programs are a arrangement of commands for the to perform
__TOC__In , acute programming, as against to , is a that describes ciphering in agreement of a and statements that change the program state. In abundant the aforementioned way as the in s expresses commands to yield action, acute programs are a arrangement of commands for the to perform
1
Function overloading (also polymorphism or adjustment overloading) is a that allows programmers to ascertain two or added with the aforementioned name.Each action has a different signature (or header), which is acquired from: function/procedure name amount of arguments argum
Function overloading (also polymorphism or adjustment overloading) is a that allows programmers to ascertain two or added with the aforementioned name.Each action has a different signature (or header), which is acquired from: function/procedure name amount of arguments argum
Ada Programming Action overloading
__TOC__/noinclude Generate_Number (MaxValue : Integer) Accumulation Random_Type Accumulation 0 .. MaxValue; Random_Pack (Random_Type); G : Random_Pack.Generator; Random_Pack.Reset (G); Random_Pack.Random (G); Generate_Number;
__TOC__/noinclude Generate_Number (MaxValue : Integer) Accumulation Random_Type Accumulation 0 .. MaxValue; Random_Pack (Random_Type); G : Random_Pack.Generator; Random_Pack.Reset (G); Random_Pack.Random (G); Generate_Number;