SQL programming example: Access database, statistics of two tables, use of count and sum aggregate functions, use of iif, and use of group

Tools used:

FineReport report designer, Access Database

Requirement Description:

There are two tables, order table and order list

1. Order table structure:

2. Order schedule structure:

Objective: To create the following table based on the two tables

Analysis:

1. Because the order ID may appear multiple times in the order details, you must first deduplicate the order ID. Otherwise, the Order ID will be repeated when two tables are joined, an error occurs when you use the count function to calculate the order quantity.

Therefore, the Order List is first grouped by order ID:

(Select order ID, sum (quantity * unit price * (1-discount) as order sales from order details group by order ID)

Then join the two tables:

Select list. order ID, order year, order month, order sales, whether to pay from (SELECT order ID, year ("order date") as order year, month ("order date ") as order month, whether to pay from order) as list, (select order ID, sum (quantity * unit price * (1-discount) as order sales from order details group by order ID) as details where list. order ID = details. order ID

2. Next, you need to group the "summary table" obtained above by year and month, and count (Order ID) for order quantity, the sum aggregate function calculates the number of paid orders by summation with conditions.

Answer:

Select order year, order month, count (Order ID) as order quantity, sum (order sales * iif (whether paid =, 0) as paid order quantity, sum (order sales) as sales from (select list. order ID, order year, order month, order sales, whether to pay from (SELECT order ID, year ("order date") as order year, month ("order date ") as order month, whether to pay from order) as list, (select order ID, sum (quantity * unit price * (1-discount) as order sales from order details group by order ID) as details where list. order ID = details. order ID) where order year =$ {year} group by order year, order month

Partial explanation:

1. year and month are built-in functions of the sail software designer. For example, the year function is used to get 2014 of the year from

2. iif (whether paid =, 0) is an Access database condition judgment. The function is to return 1 if paid = 1; otherwise, 0 is returned.

3. $ {year} is the passed parameter.