Skip to main content

Selecting COUNT from MySQL table by first letter of a column


Method 1:
SELECT count(*) total,
    SUM(case when trim(last_name) like 'a%' then 1 else 0 end) A,
    SUM(case when trim(last_name) like 'b%' then 1 else 0 end) B,
    SUM(case when trim(last_name) like 'c%' then 1 else 0 end) C,
    SUM(case when trim(last_name) like 'd%' then 1 else 0 end) D,
    SUM(case when trim(last_name) like 'e%' then 1 else 0 end) E,
    SUM(case when trim(last_name) like 'f%' then 1 else 0 end) F,
    SUM(case when trim(last_name) like 'g%' then 1 else 0 end) G,
    SUM(case when trim(last_name) like 'h%' then 1 else 0 end) H,
    SUM(case when trim(last_name) like 'i%' then 1 else 0 end) I,
    SUM(case when trim(last_name) like 'j%' then 1 else 0 end) J,
    SUM(case when trim(last_name) like 'k%' then 1 else 0 end) K,
    SUM(case when trim(last_name) like 'l%' then 1 else 0 end) L,
    SUM(case when trim(last_name) like 'm%' then 1 else 0 end) M,
    SUM(case when trim(last_name) like 'n%' then 1 else 0 end) N,
    SUM(case when trim(last_name) like 'o%' then 1 else 0 end) O,
    SUM(case when trim(last_name) like 'p%' then 1 else 0 end) P,
    SUM(case when trim(last_name) like 'q%' then 1 else 0 end) Q,
    SUM(case when trim(last_name) like 'r%' then 1 else 0 end) R,
    SUM(case when trim(last_name) like 's%' then 1 else 0 end) S,
    SUM(case when trim(last_name) like 't%' then 1 else 0 end) T,
    SUM(case when trim(last_name) like 'u%' then 1 else 0 end) U,
    SUM(case when trim(last_name) like 'v%' then 1 else 0 end) V,
    SUM(case when trim(last_name) like 'w%' then 1 else 0 end) W,
    SUM(case when trim(last_name) like 'x%' then 1 else 0 end) X,
    SUM(case when trim(last_name) like 'y%' then 1 else 0 end) Y,
    SUM(case when trim(last_name) like 'z%' then 1 else 0 end) Z,
    SUM(case when trim(last_name) REGEXP '^[[:digit:]]' then 1 else 0 end) num FROM users WHERE 1

Method 1:
SELECT
    SUBSTRING(last_name, 1, 1) as letter, count(*) as total FROM
    users GROUP BY SUBSTRING(last_name, 1, 1);
 
Labels:

Comments

Post a Comment

Popular posts from this blog

How to create a Barcode Using PHP Barcode 128 Generator

A barcode is an optical, machine-readable, representation of data, the data usually describes something about the object that carries the barcode.We will use PHP to generate Barcode in this tutorial. In this script, we are using coding which will generate barcodes in barcode format Code 128 . First, we will create index.php which will ask for the user input for which Barcode has to be created PHP Barcode Generator <fieldset><legend>Detail Informations</legend><form action="createbarcode.php" method="post"><b>Enter Your Code </b><input name="barcode" type="text" /><input type="submit" value="Create Barcode" /></form></fieldset> Now we will create createbarcode.php which will call function from Barcode code128 class for creating barcode <? php include('barcode128.php'); // include php barcode 128 class // design our barcode display echo...
2 comments
Read more

How to Change Currency Symbol Position - Magento 2

The default Magento 2 shows the currency symbol on the left side. However it does not have a feature to change the currency location from left to right.    Programmatic solution to change currency symbol position in Magento 2.   Solution to Change Currency Symbol Position in Magento 2 Create events.xml file at app/code/vendor/Exenstion/etc/frontend   <?xml version="1.0"?> <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:noNamespaceSchemaLocation="urn:magento:framework:Event/etc/events.xsd">     <event name="currency_display_options_forming">         <observer name="change_currency_position" instance="vendor\Exenstion\Observer\ChangeCurrencyPosition"/>     </event> </config>  Create ChangeCurrencyPosition.php file at app/code/vendor/Exenstion/Observer      <?php namespace vendor\Exensti...
3 comments
Read more

Symbols Used in Excel Formula | The purpose of Symbols in Excel Formula

Following symbols are used in Excel Formula. They will perform different actions in Excel Formulas and Functions. Each of these special characters have used for different purpose in Excel. Symbol Name Description = Equal to Every Excel Formula begins with Equal to symbol (=). Example: = B1+B6 () Parentheses All Arguments of the Excel Functions specified between the Parentheses. Example: =COUNTIF ( B1:B5,5 ) () Parentheses Expressions specified in the Parentheses will be evaluated first. Parentheses changes the order of the evaluation in Excel Formula. Example:   =25+ ( 35*2 ) +5 * Asterisk Wild card operator to to denote all values in a List. Example:   =COUNTIF(B1:B5,” * “) , Comma List of the Arguments of a Function Separated by Comma in Excel Formula. Example:   =COUNTIF(B1:B5 , “>” &C1) & Ampersand Concatenate Operator to connect two strings into one in Excel Formula. Example:   =”Total: “ & SUM(C2:C25) $ Dollar Makes Cell Reference as Absolute in ...
1 comment
Read more