Oracle GROUP BY ROLLUP query structure

Source: Internet
Author: User
Tags join

There are the following questions in the 047 questions

11.View the Exhibit and examine the descriptions of order_items and ORDERS tables.

You are want to display the customer_id, product_id, and Total (Unit_price multiplied by

QUANTITY) for the order placed. You are also want to display the subtotals for a customer_id as.

For a product_id to the last six months.

Which SQL statement Would you execute to get the desired output?

A. SELECT o.customer_id, oi.product_id, SUM (oi.unit_price*oi.quantity) "Total"

From Order_items oi JOIN orders O

On oi.order_id=o.order_id GROUP by ROLLUP (o.customer_id,oi.product_id) WHERE months_between (order_date, sysdate) <= 6;

B. SELECT o.customer_id, oi.product_id, SUM (oi.unit_price*oi.quantity) "Total"

From Order_items oi JOIN orders O

On oi.order_id=o.order_id GROUP by ROLLUP (o.customer_id,oi.product_id) has Months_between (order_date, sysdate) < = 6;

C. SELECT o.customer_id, oi.product_id, SUM (oi.unit_price*oi.quantity) "Total"

From Order_items oi JOIN orders O

On oi.order_id=o.order_id GROUP by ROLLUP (o.customer_id, oi.product_id) WHERE months_between (order_date, sysdate) >= 6;

D. SELECT o.customer_id, oi.product_id, SUM (oi.unit_price*oi.quantity) "Total"

From Order_items oi JOIN orders O

On oi.order_id=o.order_id WHERE months_between (order_date, sysdate) <= 6 GROUP by ROLLUP (o.customer_id, oi.product_id ) ;

Answer:d

In the above problem to choose the correct answer is very simple, that is, group by should be placed in the back of the where, but the problem appears in a knowledge point we need to pay attention to, is the use of Rollup.

Everyone is familiar with the group by basic syntax, for example, to count the highest salary of employees in each department, you can use

Select Max (SAL) from the EMP group by DEPTNO;

However, if there are more complex statistics, the group by basic usage is not satisfied with the requirements, but also with rollup to use, such as:

Need to install position, manager, department to count employee's total salary, and do a sub statistic for position and manager

Sql> Select Job,mgr,deptno,sum (SAL) from EMP where Deptno into (10,30) group by Rollup (JOB,MGR,DEPTNO);

JOB MGR DEPTNO SUM (SAL)

--------------------------- ---------- ---------- ----------

Clerk 7698 30 950

Clerk 7698 950

Clerk 7782 10 1300

Clerk 7782 1300

Clerk 2250

MANAGER 7839 10 2450

MANAGER 7839 30 2850

MANAGER 7839 5300

MANAGER 5300

Salesman 7698 30 5600

Salesman 7698 5600

JOB MGR DEPTNO SUM (SAL)

--------------------------- ---------- ---------- ----------

Salesman 5600

PRESIDENT 10 5000

PRESIDENT 5000

PRESIDENT 5000

18150

Rows selected.

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.