Home
Analogues
This section contains 1010data analogues for Excel and SQL users.
1010data for 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.
Introduction
Data Types
The data types offered in both SQL and 1010data differ. It is important to know how these data types should be converted in between systems.

Analogues
This section contains 1010data analogues for Excel and SQL users.
- 1010data for Excel users
  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.
- 1010data for 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.
  - Introduction
    - Workflow
    - Data Types
      The data types offered in both SQL and 1010data differ. It is important to know how these data types should be converted in between systems.
  - Function comparisons
  - Counting occurrences
    Based on specified groups in your data, you can determine how many times pairs of values occur together.
  - Counting based on conditions
    You can determine how many times a specified value occurs in a table based on predetermined and conditional requirements.
  - Calculating group specific averages
    Instead of calculating one overall average, you can calculate smaller averages based on criteria specific groups.
  - Selecting based on rank
    You can select values based on certain criteria, for example if their rank puts them in a certain range.
  - Time comparison analysis by groups
    You can compare aggregated data for two different time periods, even if that data is contained in the same column.
  - Performing an inner join
    An INNER JOIN in SQL is similar to a link and select in 1010data.
  - Performing a left or right join
    With some manipulation, a simple link in 1010data can produce the same results as a LEFT JOIN or a RIGHT JOIN in SQL.
  - Performing a union
    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.
  - Performing a union all
    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.
  - Denormalizing tables
    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.
  - Transposing a table
    In order to transpose a table using SQL, multiple operations are required. Using 1010data, you can transpose a table with one basic operation.

Data Types

The data types offered in both SQL and 1010data differ. It is important to know how these data types should be converted in between systems.

Table 1. Data Type Equivalents
SQL Data Type	1010data Type
Integers	Integer (i)
Byteints
Smallints
Booleans
Dates
Times
Timestamps (date + time)	Decimals /Floats (f)
Bigints (where the max absolute value is less than 8,000,000,000,000)
Numerics
Bigints (where the max absolute value is greater than 8,000,000,000,000)	String/Alphanumeric (a)
Char
Varchar