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.
Really nice clarification on similarities. Makes the concepts in SQL clearer. Thank you.