GROUP BY Statement

The GROUP BY statement is used to group the records fetched in a search query results by one or more columns. GROUP BY enables identical data to be grouped together and displayed sequentially. When there are duplicate values in a column, the GROUP BY statement displays the duplicates together. The records are then ordered based on other columns.

The GROUP BY statement is associated with the SELECT statement and is often used with ZCQL functions. It is used towards the end of the query and should therefore satisfy the JOIN statements and follow the WHERE conditions.

The basic syntax for using a GROUP BY statement along with the SELECT statement is as follows:

    

        
copy
        

            

        
    
    

         SELECT column_name(s) FROM parent_table_name GROUP BY column_name(s)

    

Example:

To view a list of theaters and their locations from the Theaters table, grouped by the location of the theaters, execute the following query:

    

        
copy
        

            

        
    
    

         SELECT TheaterName, Location FROM Theaters GROUP BY Location 
    

This will generate the following output:

BINARYOF() Function

The BINARYOF() function can only be used in a GROUP BY statement. The function can be used in places where you wish to produce a output that is not case sensitive and considers all the values in a coulumn of a table.

The BINARYOF() function can only be used on columns with VarChar or Text datatype.

For example, consider the following table Zylker_EMP:

An example ZCQL statement to illustrate the functionality of BINARYOF() and its possible output is given below:

    

        
copy
        

            

        
    
    

         SELECT Names FROM Zylker_EMP GROUP BY BINARYOF(Names)
    

ORDER BY Statement

The ORDER BY statement is used to sort the records fetched in a search query results in an ascending or a descending order, based on one or more columns. When the ORDER BY statement is used to sort textual data, it sorts the records in the alphabetical order.

Similar to the GROUP BY statement, the ORDER BY statement is associated with the SELECT statement and is often used after the GROUP BY statement. It is used towards the end of the query, after the JOIN clause statements or the WHERE conditions, if present, but before the LIMIT clause. The ORDER BY statement should then satisfy the JOIN statements and follow the WHERE conditions.

• If there are duplicate values in a column, the ORDER BY statement will display the duplicates together. The records are then ordered based on other columns.

• ZCQL Functions can also be used with ORDER BY statements.

The basic syntax for using a ORDER BY statement with the SELECT statement is as follows:

    

        
copy
        

            

        
    
    

         SELECT column_name(s) FROM parent_table_name ORDER BY column_name(s) [ASC | DESC]
    

Example:

To view a list of the ticket prices from the Pricing table in descending order for each movie from the Movies table execute the following query:

    

        
copy
        

            

        
    
    

         SELECT Movies.MovieName, Pricing.Price FROM Movies
INNER JOIN Pricing ON Movies.MovieID = Pricing.MovieID
ORDER BY Pricing.Price DESC 
    

This will generate the following output: