This section contains 1010data analogues for Excel and SQL users.
1010data users have backgrounds using different programming languages, databases, and analytical tools. One of the most widely used tools for data is SQL. This section of the 1010data Cookbook was created to help the users with SQL background learn how to complete tasks in 1010data. Each analogue will describe a problem that the user knows how to complete in SQL, and then show how to solve the problem in 1010data.
1010data can solve the same problems that you are used to solving in Excel in one or more ways. Sometimes there are direct translations between the two, and sometimes a unique solution is needed in order to arrive at the same result.
Based on specified groups in your data, you can determine how many times pairs of values occur together.
You can determine how many times a specified value occurs in a table based on predetermined and conditional requirements.
Instead of calculating one overall average, you can calculate smaller averages based on criteria specific groups.
You can select values based on certain criteria, for example if their rank puts them in a certain range.
You can compare aggregated data for two different time periods, even if that data is contained in the same column.
An INNER JOIN in SQL is similar to a link and select in 1010data.
INNER JOIN
With some manipulation, a simple link in 1010data can produce the same results as a LEFT JOIN or a RIGHT JOIN in SQL.
LEFT JOIN
RIGHT JOIN
A UNION in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. During this union, all duplicates are removed.
UNION
merge
A UNION ALL in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. With this variation of UNION all rows, including duplicates, are retained in the final worksheet.
UNION ALL
In SQL it is very common to have smaller tables that are used as reference tables to a larger data set. This allows the data to be stored easier and accessed faster. However, if you want to perform any calculations that involve these reference tables in addition to your main table, you must first combine them, or denormalize them.
In order to transpose a table using SQL, multiple operations are required. Using 1010data, you can transpose a table with one basic operation.
