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

Source: Internet
Author: User

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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.