Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Standard Deviation

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       Standard Deviation

      MySQL Standard Deviation Functions are mathematical functions that are used to calculate the variation or dispertion between values in a dataset.
      There are two types of standard deviations in MySQL, they are population standard deviation and sample standard deviation.

      Popular standard deviation

      The "population standard deviation" is the square root of the variance of a set of data. It calculates the amount of variation or dispersion within a population. Symbolically it is represented by σ (the Greek letter sigma).
      To calculate population standard deviation, we can use the following functions:
      • STD(expression): It calculates and returns the population standard deviation the fields in a particular column. If the specified row(s) doesn't exist this function returns NULL.
      • STDDEV(expression): It is same as STD() function, but it also works with oracle database.
      • STDDEV_POP(expression): It is equivalent to STD() function.
      Following is the mathematical formula to calculate the "population standard deviation":
      // Mathematical Formula
      $\sigma = \sqrt{\frac{\sum_{i=1}^n{(x-\bar x)^2}}{N-1}}$
      Where,
      • σ = population standard deviation
      • N = size of the population
      • Xi = each value from the population
      • meu = the population mean

      Example

      First, let us create a table with the name CUSTOMERS using the CREATE statement as shown below 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(15) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS VARCHAR(25),
         SALARY DECIMAL(10, 2),
         PRIMARY KEY(ID)
      );
      Now, let us insert values into the CUSTOMERS table using the INSERT statement 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', '32', 'Ahmedabad', 2000),
      (2, 'Khilan', '25', 'Delhi', 1500),
      (3, 'Kaushik', '23', 'Kota', 2000),
      (4, 'Chaitali', '26', 'Mumbai', 6500),
      (5, 'Hardik','27', 'Bhopal', 8500),
      (6, 'Komal', '22', 'Hyderabad', 9000),
      (7, 'Muffy', '24', 'Indore', 5500);
      The table is created as
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00

      The STD() Function

      The following query calculates the population standard deviation of scores of all players in exhibition match 
      SELECT STD(AGE) from CUSTOMERS;

      Output

      Following is the output
      STD(AGE)
      3.063944369932459

      The STDDEV() Function

      The STDDEV() function is the same as STD() function, but it will also work with oracle database.
      In the following query, we are calculating the population standard deviation on "Score_In_Exhibition_Match" column 
      SELECT STDDEV(AGE) FROM CUSTOMERS;

      Output

      The output is produced as follows
      STDDEV(AGE)
      3.063944369932459

      The STDDEV_POP() Function

      In MySQL, the STDDEV_POP() function is equivalent to the STD() function. Here, we are performing the population standard deviation on AGE column of CUSTOMERS table.
      SELECT STDDEV_POP(AGE) FROM CUSTOMERS;

      Output

      The output is displayed as follows
      STDDEV_POP(AGE)
      3.063944369932459

      Sample Standard Deviation

      The MySQL standard deviation is the square root of the variance, which calculates how dispersed or spread out the data is.
      The STDDEV_SAMP() function is used to calculate the sample standard deviation of a set of values in a column.
      Following is the formula to calculate the "Sample Standard Deviation":
      // Mathematical formula
      s = sqrt(sum((x - mean)^2) / (n - 1))

      Example

      In the following example, let us calculate the sample standard deviation on the AGE column of the previously created CUSTOMERS table 
      SELECT STDDEV_SAMP(AGE) FROM CUSTOMERS;

      Output

      The output is displayed as follows
      STDDEV_SAMP(AGE)
      3.309438162646486
      P