Home / Programming and Reporting / Sort and Filter the Data by SQL

Sort and Filter the Data by SQL

Can we sort the data based on a column?

Select * from table ORDER BY

  • To select a table and sort it based on a column
  • Here we just have to say ORDER BY and then the column name which we want to take as reference for ordering or sorting and based on that, the table will be sorted in ascending or descending order.
  • Get the product table from classic models database make sure that the resultant table is sorted based on MSRP
        select * from  table_name ORDER BY col_name
        select * from classicmodels.products ORDER BY MSRP;
  • All the data or records from the products table will be sorted with respect to the MSRP column in the ascending order.

Select * from table ORDER BY descending

  • To select a table and sort it based on a column in descending order
  • Get the product table from classic models database make sure that the resultant table is sorted based on MSRP in descending order
        select * from  table_name ORDER BY col_name DESC
        select * from classicmodels.products ORDER BY MSRP DESC;

Can we fetch some summary statistics instead of data ?

Select Count(*) from table

  • To fetch number of rows
  • Get the row count of product table from classic models database
        select count(*) from table_name; 
        select count(*) from classicmodels.products;
  • This will give the total number of records or the total number of rows in the table.

Select Count(col_name) from table

  • To fetch number of rows based on a coloumn
  • Get the row count based on product code from product table in classic models database
        select count(col_name) from table_name; 
        select count(productCode) from classicmodels.products;

Select sum(col_name) from table where

  • To fetch the sum of values in a column
  • Get the sum of quantityInStock from product table in classic models database
        select sum(col_name) from table_name; 
        select sum(quantityInStock) from classicmodels.products;

Select avg(col_name) from table

  • To fetch the average of a column
  • Get the average of MSRP from product table in classic models database
        select avg(col_name) from table_name; 
        select avg(MSRP) from classicmodels.products;

Can we fetch some summary statistics grouped into segments. For example counts of east, west, north and south regions

Select count(*) from table group by col_name

  • To fetch count for groups of values in a column
  • What if there is a variable called region and for each region, i.e., east, west, south and north, we want to know the summary statistics of a particular column.
  • Get the counts of records for each product line. Frequency of each product line
        select col_name, count(*) from table_name GROUP BY col_name; 
        select productLine, count(*) from classicmodels.products GROUP BY productLine;

Select avg(col_name) from table group by col_name

  • To fetch average of a column for groups of values in a column
  • Get the average MSRP for each product line.
        select col_name1, avg(col_name2) from table_name GROUP BY col_name1; 

        select productLine, avg(MSRP) from classicmodels.products GROUP BY productLine;

GROUP BY and ORDER BY

  • Its is always better to order the results of the summaries that we get i.e., in ascending or descending order. For this purpose, we use a function called “order by”. -Get the average MSRP for each product line. Order the result based on average MSRP
        select productLine, avg(MSRP) as avg_price from classicmodels.products GROUP BY productLine ORDER BY avg_price
  • If we want to order it in descending order, then just mention DESC at the end.

About admin

Check Also

sql

Introduction to SQL- Basics

  SQL Basics Contents SQL Databases Sql Basics RMySql Sql Queries Sql Joins What is …

Leave a Reply

Your email address will not be published. Required fields are marked *