### group from

#### cumulative function

Calculate the quantity of the product:

choose numbering( *) from product;

Count the number of products with a non-zero value:

choose numbering( price) from product;

Count the number of unique ones`category`

Values:

choose numbering( clearly category) from product;

Get minimum and maximum product prices:

choose Min. ( price) ,upper limit ( price) from product;

Find the total product price for each category:

choose category,and( price) from productgroup from category;

Find the average price of each product category whose average price is above 3.0:

choose category,average value ( price) from productgroup from categoryI have average value ( price) > 3,0

### Order by

Get product names sorted by order`price`

Columns sorted in ascending order by default:

choose Namefrom productOrder by price[ ASC ] ;

Get product names sorted by order`price`

Columns in descending order:

choose Namefrom productOrder by priceDESC ;

### calculate

use`+`

,`-`

,`*`

,`/`

Do basic math. To get the seconds of the week:

choose 60*60*24*7;-- Result: 604800

#### round numbers

Round the numbers to the nearest whole number:

choose round (1234.56789);-- Result: 1235

Round numbers to two decimal places:

choose round ( avg ( value ), 2 )from productWhere class_id = 21;-- Result: 124.56

### troubleshooting

#### integer division

In PostgreSQL and SQL Server,`/`

The operator performs integer division on integer arguments. If you don't see the expected number of decimal places, it's because you're dividing between two integers. Convert one to decimal:

123/2-- Result: 61 throw ( 123 AS Decimal) / 2-- Result: 61.5

#### divide by 0

To avoid this error, make sure the denominator is not`0`

. you can use it`NULLIF()`

function to replace`0`

with`invalid`

, which leads to`invalid`

For the whole expression:

counting /NULLIF( all counts, 0)

### take part

JOIN is used to get data from multiple tables. To get the names of the products purchased in each order, use:

choose Order.OrderDate, Product Nameas Quantityfrom Seriestake part productexists product.id = order.product_id;

Learn more about JOIN in our interactionSQL connectioncourse.

### enter

To insert data into the table, use`enter`

Series:

enter categoryvalues (1, "Home and Kitchen"), (2, "Clothes and Clothing");

You can specify the columns to add data to. The remaining columns are filled with predefined default values or`invalid`

small.

enter category name)values (“electronic product”);

### renew

To update data in a table, use`renew`

Series:

renew categoryI put is_active = true, name = 'office'Where name = 'office';

### delete

To delete data from a table, use`delete`

Series:

deleted by categoryWhere NameVacuum ;

Check out our interactive lessonsHow to insert, update and delete data in SQL.

### date and time

There are 3 main types related to time:

,

, and

. Times are represented using a 24-hour clock and can be as vague as hours and minutes (for example,`15:30`

– 3:30 PM) or to the nearest microsecond and time zone (see below):

`14:39:53.662522-05`

It's almost 2:40pm. CDT (eg in Chicago, 7:40 PM UTC). The letters in the example above represent:

**In the date part:**

`YYYY`

– 4 digit year.`mm`

– Months with zero investment (`01`

——January to`12`

-December).`DD`

– Day with zero investment.**part of time:**

`fearful`

– Zero hours on a 24-hour clock.`mm`

- Minutes.`SS`

- The number of seconds.*can be omitted*.

`ssssssss`

– Small fraction of a second – can be represented using 1 to 6 digits.*can be omitted*.

`±TZ`

- Time zone. It must start with any of the following`+`

the`-`

and use two digits relative to UTC.*can be omitted*.

#### current date and time

Find out what time it is:

choose current time ;

Get today's date:

choose Today's Date ;

In SQL Server:

choose getDate() ;

Get the timestamp of the current date and time:

choose CURRENT_TIMESTAMP ;

#### Creation date and time value

To create a date, time, or timestamp, write the value to a string and convert it to the correct type.

choose throw ( '31-12-2021'as date) ;choose throw ( '15:31'as year) ;choose throw ( '2021-12-31 23:59:29+02'as time stamp) ;choose throw ( '15:31.124769'as year) ;

Note the last example - it interprets as 15 minutes 31 seconds 124769 microseconds! It's always a good idea to explicitly write 00 for the time:`'00:15:31.124769'`

.

### chronological order

use`Order by`

Sort rows chronologically from oldest to newest in datetime columns:

choose Order Date, Product, Quantityfrom sales volumeOrder by date of order;

date of order | product | quantity |
---|---|---|

22-07-2023 | laptop | 2 |

23-07-2023 | biceps | 3 |

24-07-2023 | sports shoes | 10 |

24-07-2023 | Jeans | 3 |

25-07-2023 | mixer | 2 |

Sort from newest to oldest using descending order:

choose Order Date, Product, Quantityfrom sales volumeOrder by date of orderDESC ;

### Compare date and time values

You can use comparison operators`<`

,`<=`

,`>`

,`>=`

, and`=`

Compare date and time values. Earlier dates are less than later dates. For example,`2023-07-05`

less than`2023-08-05`

.

Find sales for July 2023:

choose Order date, product name, quantityfrom sales volumeWhere Order date >= '07-01-2023'and order_date < '08-01-2023';

Find customers signed up for July 2023:

choose Registration timestamp, emailfrom customerWhere Record Timestamp >= '07-01-2023'and Record Timestamp < '2023-08-01';

**notes:**Note the due date in the query. upper limit`'01-08-2023'`

Not included. time stamp`'01-08-2023'`

it is actually a timestamp`'01-08-2023 00:00:00.0'`

. comparison operator`<`

Used to ensure that the selection is made for all timestamps less than`'01-08-2023 00:00:00.0'`

, meaning all timestamps in July 2023, even those near midnight on August 1, 2023.

### space

Intervals measure the difference between two points in time. For example, the space between`2023-07-04`

and`06-07-2023`

It's 2 days.

To set a range in SQL, use the following syntax:

space "1"heaven

The pension consists of three elements:`space`

keywords, quoted values, and time segment keywords. You can use the following time parts:`Year`

,`moon`

,`heaven`

,`Time`

,`minute, thin`

, and`second`

.

#### Add spaces to date and time values

you can use it`+`

the`-`

The interval to add or subtract from a date or timestamp value.

minus one year`2023-07-05`

:

choose throw ( '05-07-2023'as time stamp) -space "1"Year ;-- Result: 2022-07-05 00:00:00

Find customers who placed their first order within a month of signing up:

choose ID cardfrom customerWhere Date of first order > Date of registration +space "1"moon ;

#### Filter events within the last 7 days

To find scheduled deliveries in the last 7 days, use:

choose delivery date, addressfrom sales volumeWhere delivery date <=Today's Date and delivery date >=Today's Date -space "7"heaven ;

**notes:**In SQL Server, intervals are not implemented - use`date added()`

and`date difference()`

Mode.

#### Filter events within the last 7 days in SQL Server

To find discounts for the last 7 days, use:

choose delivery date, addressfrom sales volumeWhere delivery date <=getDate() and delivery date >=date added (day ,-7,getDate() ) ;

### extract partial date

The standard SQL syntax for getting a part of a date is

choose refining ( Yearfrom date of order) from Sales volume;

You can export the following fields:`Year`

,`moon`

,`heaven`

,`Time`

,`minute, thin`

, and`second`

.

Standard syntax does not work in SQL Server. use`DATEPART (part, date)`

operation instead.

choose date part ( year, date of order) from Sales volume;

#### group by year and month

Find the number of sales per month:

choose refining ( Yearfrom date of order) as Year,refining ( moonfrom date of order) as moon,numbering( *) as numberingfrom sales volumegroup from yearsOrder by years;

Year | moon | numbering |
---|---|---|

2022 | 8 | 51 |

2022 | 9 | 58 |

2022 | 10 | 62 |

2022 | 11 | 76 |

2022 | 12 | 85 |

2023 | 1 | 71 |

2023 | 2 | 69 |

Note that you must also group by year and month.`Delivery (months from order date)`

Output only the month digits (1, 2, ..., 12). To distinguish months from different years, you must also group by year.

For more information about using date and time values in our interactiveStandard SQL functionscourse.

### case when

`case when`

allows you to pass conditions like`Where`

clause), evaluate them in order and return the value that satisfies the first condition.

choose Name,case never Price > 150afterward 'High quality'never value > 100afterward "middle class"but 'role model'end of AS price_categoryfrom product;

Here, all products with prices above 150 get*High quality*tag, users with a value above 100 (and below 150) will receive.*middle class*label, the rest receive*role model*Inscription.

Cases and group v

you can combine`case when`

and`group from`

Calculates object statistics in categories you specify.

choose case never Price > 150afterward 'High quality'never value > 100afterward "middle class"but 'role model'end of AS price_category,numbering( *) as productfrom productgroup from price_category;

Calculate the bulk order quantity per customer using`case when`

and`and()`

:

choose Customer's code,and( case when Quantity > 10afterward 1but 0end ) as Large Ordersfrom sales volumegroup from Customer's code;

...or use`case when`

and`numbering()`

:

choose Customer's code,numbering( case when Quantity > 10afterward order numberend ) as Large Ordersfrom sales volumegroup from Customer's code;

Learn more in our interactionCreate basic SQL reportscourse.

### group by extension

#### grouping set

`grouping set`

Allows you to specify multiple sets of columns to group by in a query.

choose area, product,numbering( order number) from sales volumegroup from grouping set ((region, product) ,() ）

#### cube

`cube`

Create groupings for all possible subsets`group from`

List.

choose area, product,numbering( order number) from sales volumegroup per cube (region, product);

#### wrapped

`wrapped`

Added new grouping levels for subsets and aggregates.

choose area, product,numbering( order number) from sales volumegroup with summary (region, product);

### merge

`merge`

replace the first one`invalid`

Argument with given value. Usually used to display labels`group from`

expand.

choose area,merge( product, "all") ,numbering( order number) from sales volumegroup with summary (region, product);

area | product | numbering |
---|---|---|

USA | laptop | 10 |

USA | biceps | 5 |

USA | all | 15 |

UNITED KINGDOM. | laptop | 6 |

UNITED KINGDOM. | all | 6 |

all | all | 21 |

Check out our practical lessonsGROUP BY extension.

### common array expression

A common array expression (CTE) is a named temporary result set that can be referenced in a larger query. They are especially useful for complex assemblies and for breaking large queries into more manageable parts.

and Total product salesas (choose product,and( profit) as Total profitfrom sales volumegroup from product)choose average value ( Total profit) from total product sales;

Check out our practical lessonscommon array expression.

### window mode

Window functions calculate their results based on a sliding window frame (a set of rows relative to the current row). Unlike aggregate functions, window functions do not collapse rows.

#### Calculate the percentage of the total within the group

choose Product, Brand, Profit, (100.0 * Profit /and( profit) Exceed( Partition base brand) )as perchlorethylenefrom Sales volume;

product | brand | profit | perchlorethylene |
---|---|---|---|

knife | kitchen | 1000 | 25 |

container | kitchen | 3000 | 75 |

toy doll | Toiz | 2000 | 40 |

car | Toiz | 3000 | 60 |

#### ranking

To sort products by price:

choose rank() over ( Order by price) , Namefrom product;

##### ranking function

`class`

– Give equal rank to associated values, leaving spaces.`DENSE_RANK`

– Give the same rank to bound values without spaces.`ROW_NUMBER`

– Give consecutive numbers without spaces.

Name | class | Intensive level | Row number |
---|---|---|---|

Jeans | 1 | 1 | 1 |

gaiters | 2 | 2 | 2 |

gaiters | 2 | 2 | 3 |

sports shoes | 4 | 3 | 4 |

sports shoes | 4 | 3 | 5 |

sports shoes | 4 | 3 | 6 |

Short-sleeved blouse | 7 | 4 | 7 |

##### running total

A running total is the cumulative sum of a given value and all previous values in the column.

choose date, amount,and( quantity) Exceed( Order by date) as running totalfrom Sales volume;

##### moving average

moving average (*aka*The running average is a technique for analyzing trends in time series data. It is the average of the current price and a specified number of previous prices.

choose date, price,average value ( price) Exceed( Order by datebetween the lines 2forward and the current row ) as moving averagefrom share price?

##### Difference (Delta) between two series

choose Annual income,back( income) Exceed( sorted by year) as Revenue_previous_year, Revenue -back( income) Exceed( sorted by year) as difference annuallyfrom annual indicators;

Learn about SQL window functions in our interactivewindow modecourse.