You are currently viewing Common SQL syntax used in Microsoft Excel

Common SQL syntax used in Microsoft Excel

When it comes to data analytics, SQL is one language you will always come across. SQL is a standard language for accessing and manipulating databases. It uses simple sets of English words to interact with databases. Thus, it is easy to read, write, and pick up. 

Today, we will look at some common SQL syntax and the Microsoft Excel alternative function.

SQL syntaxes and their Microsoft Excel alternatives

 

Function/Syntax name SQL syntax Excel function
SUM returns the sum of values supplied.  Select SUM (Country) from Country SUM(number1,[number2],…)
COUNT returns the number of rows or count of records.  Select COUNT (Country) from Country COUNT(value1, [value2], …)

COUNTA(value1, [value2], …)

FILTER returns a subset of the data. SELECT * FROM

Sales_orders

WHERE Country = ’Singapore’

FILTER(array,include,[if_empty])
SORT arranges your data either alphabetically or in an ascending or descending order. SELECT * FROM

Sales_orders

WHERE Country_’Singapore’

ORDER BY Quantity

=SORT(array,[sort_index],[sort_order],[by_col])
UPPER converts the data to uppercase. Select UPPER (Country) from Country UPPER(text)
LOWER convert the data to lowercase. Select LOWER (Country) from Country LOWER(text)
NOW returns the date and time. Select NOW(); NOW()
CONCATENATE merges two or more strings together.  SELECT Age, CONCAT(Last_name, ‘ ‘, First_name) AS Full_name

FROM Sales_orders’

CONCATENATE(text1, [text2], …)
VLOOKUP is used to find things in a table or a range by row. SELECT P.*,  S.Customer_name, FROM

Sales_orders

JOIN Sales_Products P ON P.Product_code =S.Product_code

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
IF allows you to make logical comparisons between a value and what you expect. SELECT Order_ID, Total_sales, CASE WHEN  Total_sales <= 1000 THEN ‘low’

WHEN Total_sales > 1000 AND Total_sales <= 5000

THEN ‘Medium’

WHEN Total_sales > 5000 THEN ‘High’

ELSE END sales_value

FROM Sales_orders’

IF(logical_test, value_if_true, [value_if_false])
SUMIF returns the sum of the values in a range that meets the specific criteria. SELECT SUM(total_sales) AS total_sales FROM

Sales_orders

WHERE Country_’Singapore’

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
LEN returns the number of characters in a text. SELECT S.Customer_name, length(Customer_name) AS Len FROM

Sales_orders

LEN(text)
FIND return the position of a specified character within a text. SELECT Customer_name, Position( ‘T’ IN Customer_name) FROM

Sales_orders

FIND(find_text, within_text, [start_num])
LEFT extract characters from the left most side of a text SELECT Customer_name, SUNSTR(Customer_name,1,3) AS Suffix FROM

Sales_orders

LEFT(text)
PIVOT rotates rows in a table by turning row values into multiple columns.  SELECT (ColumnNames) 

FROM (TableName) 

PIVOT

 ( 

   AggregateFunction(ColumnToBeAggregated)

   FOR PivotColumn IN (PivotColumnValues)

 ) AS (Alias)

Pivot table or Pivot in Power Query

 

Final Thought

This blog looks at some functions SQL, and Excel have in common and what these functions do. 

To learn more about Microsoft Excel or SQL, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.

This Post Has One Comment

  1. Virtue Aans

    Really nice clarification on similarities. Makes the concepts in SQL clearer. Thank you.

Leave a Reply