MySales modules

Basic SQL QueriesCapabilities

To create basic reports, user needs a basic understanding of how sql queries work. Today there are several SQL dialects, the documentation will detail queries in the T-SQL dialect, which is used in the MS SQL Server DBMS.

94%
Forecast Accuracy

Established product accuracy at Drogas

15-20%
Revenue Growth

Turnover increase at Chudo Market after deployment

40%
Waste Reduction

Fresh write-off reduction at Blyzenko

AI Platform

One AI platform connects forecasting, replenishment, pricing, and promos - signals flow automatically between modules, so orders, prices, and campaigns stay in sync.

Module overview

Overview

Below will be described main sql commands that are required for basic work with sql scripts - SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY.

An excellent SQL manual with examples and the ability to try different commands is given at link .

SELECT SELECT - this is the basic command used to select data from the database.

This command is usually used with the FROM command, which specifies which table to fetch data from.

It is also possible to select from several tables - this will be described in the section [JOIN]( https://mysales- labs.atlassian.net/wiki/spaces/CS/pages/11862221/Basic+SQL+Queries#JOIN).

The default SELECT query looks like this: SELECT column1, column2, ... FROM table_name; column1, column2, ...

in this query, these are the names of the fields (columns) in the table from which user wants to get data.

In case user needs to select all data, use *: SELECT * FROM table_name; For example, the query

Business impact

Impact We Deliver

SELECT sku_id, article_name FROM products; there will be two columns with the SKU code and the name from the PRODUCTS table: And the query result SELECT * FROM products; there will be all columns from the PRODUCTS table: When creating SELECT queries, it is advisable to assign the aliases to columns (names): SELECT column_1 SKU, column_2 STORE FROM table_name; .

In this example, SKU and STORE are aliases of columns column_1 and column_2 - they will be further used in the work with the report designer.

Learn more about SELECT and try how to work with it at link . User can learn more about the concept of aliases at link .

In addition to the standard field selection with SELECT, you can also select specific data.

Following are the basic functions that can be used directly in a SELECT query.

WHERE The WHERE command is used to create a selection condition: SELECT column1, column2, ...

FROM table_name WHERE condition; For example, if you need to select all values that are greater than 10, then the WHERE clause should be indicated: SELECT column1 FROM table_name WHERE column1 > 10; Also, the WHERE command has mathematical operators such as greater than, less than, not equal, and so on.

They are used when setting a condition. Learn more about WHERE, available operators and try how to work with it at link .

There can be several conditions in WHERE, for their definition the logical operators "AND", "OR", "NOT" are used.

If it is necessary that both one and the other conditions are met, AND is used.

If it is necessary that one of several conditions be met, OR is used.

If you want the data to be displayed when the condition is not met, NOT is used.

Learn more about AND, OR, NOT and try how to work with it at link .

The IN and LIKE operators are comparison operators in the WHERE clause.

Also in WHERE, user can work with columns that have NULL values (a field that has no value).

The standard WHERE clauses do not work with NULL. You must use the IS NULL and IS NOT NULL operators.

Learn more about IS NULL and IS NOT NULL and try how to work with it at link .

ORDER BY ORDER BY command allows user to sort the data output according to certain rules. SELECT column1, column2, ...

FROM table_name WHERE condition ORDER_BY column1 asc/desc Learn more about ORDER BY and try how to work with it at link .

JOIN The JOIN command is used after the FROM command and is used to display columns in a query from different tables that are related to each other.

So for example, if there is a PRODUCTS table with SKU_ID, GROUP_ID columns and there is a GROUPS table with GROUP_ID and GROUP_NAME columns, the JOIN will allow user to select SKU_ID and GROUP_NAME data from different tables, while linking them by GROUP_ID.

There are several types of JOINs: Learn more about JOIN and try how to work with it at link .

User can study and try each type of JOIN in more detail by following the links: (INNER) JOIN , LEFT JOIN , RIGHT JOIN , FULL JOIN .

GROUP BY The GROUP BY command is used after the WHERE command and is used to group data by similarity.

So, for example, user can group all products that have the same code.

It is used if there are aggregating functions after SELECT in the query SELECT column1, sum(column2) FROM table_name WHERE condition GROUP BY column1 ORDER_BY column1 asc/desc A good example of using GROUP BY in case user needs to display, for example, sales for 4 weeks (from 10th to 13th week of 2019) in the context of the selected SKU for two Stores.

In the SALES_SKU table, sales are broken down by week, we need to sum the sales for 4 weeks to get the sales for 4 weeks.

For such a case, the request will look like this: SELECT sku_id, store_id, sum(sales_volume) vol FROM sales_sku WHERE sku_id = 93 and week between 201910 and 201913 and store_id in (1,2) GROUP BY sku_id, store_id ORDER_BY vol asc The result of executing such a query is as follows: It is important that GROUP BY must be specified not only by SKU_ID, but also by STORE_ID, since these two fields are not used in the aggregation function.

And if user select only GROUP BY SKU_ID, then the system will give an error that STORE_ID is not applied neither in GROUP BY nor in any other aggregation function.

Learn more about GROUP_BY and try how to work with it at link .

  • SELECT DISTINCT - used to select unique values for a field. for instance:
    SELECT DISTINCT column_1 FROM table_name;.
    This query will print only unique values in column_1.
    Learn more about SELECT DISTINCT and try how to work with it at link.

  • SELECT TOP - is used to select the first N values from a field. For instance:
    SELECT TOP 100 column_1 FROM table_name;.
    This query will print only the first 100 values in column_1.
    Learn more about SELECT TOP and try how to work with it at link.

  • MIN/MAX - used to display the smallest or largest values in a column. For instance:
    SELECT MIN(column_1) FROM table_name;.
    This query will print the smallest value in column_1.
    Learn more about MIN / MAX and try how to work with it at link.

  • COUNT, AVG, SUM - COUNT () counts the number of rows in a column; AVG () displays the average of the column; SUM () - displays the sum of all values.
    SELECT COUNT(column_1) FROM table_name;.
    This query will print the number of rows in column_1.
    SELECT AVG(column_1) FROM table_name;.
    This query will print the average in column_1.
    SELECT SUM(column_1) FROM table_name;.
    This query will print the sum of all values in column_1.
    Learn more about COUNT, AVG, SUM and try how to work with it at link.

  • IN allows sampling when there are multiple values, for example:
    SELECT SKU_ID, ITEM_CODE FROM PRODUCTS WHERE SKU_ID in (2,4,8)
    This query will display only those SKUs whose SKU_ID is 2, 4, or 8.
    Learn more about IN and try how to work with it at link.

  • LIKE allows user to search for a given pattern in data from a column, for example:
    SELECT SKU_ID, ARTICLE_NAME FROM PRODUCTS WHERE ARTICLE_NAME like (%Ham%)
    This query will display those SKUs with the word "Ham" in their name.
    Learn more about LIKE and try how to work with it at link.

  • (INNER) JOIN - can be used with or without the INNER prefix - displays only those records that have the same values in both tables;

  • LEFT JOIN - displays all records from the left table (as specified by FROM) and corresponding records from the right table (as specified by JOIN);

  • RIGHT JOIN - displays all records from the left table (as specified by JOIN) and corresponding records from the right table (as specified by FROM);

  • FULL JOIN - displays all records if there is a match in the left or right table;

  • such as COUNT, MAX, MIN, SUM, AVG, etc.