Question [SQL]: How can I list all books with prices greather than the average price of books of the S

Source: Internet
Author: User

Question: How can I list all books with prices greather than the average price of books of the same type?
In database pubs, have a table named titles, its column named Price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:

Program Code
Type title price
-----------------------------------------------------------------------------------------------------------------
Business the busy executive's database guide 19.9900
...
...
...

...

 

Answer:

Select A. [ Type ] ,. [ Title ] ,. [ Price ]
From   [ Pubs ] . [ DBO ] . [ Titles ] A,
( Select   [ Type ] , [ Price ] = AVG ( [ Price ] ) From   [ Pubs ] . [ DBO ] . [ Titles ]   Group   By   [ Type ] ) B
Where A. [ Type ] = B. [ Type ]   And A. [ Price ] > B. [ Price ]

 

Here is a similar question:

The first question is relatively simple. to query the sales table, use oneSQLStatement.

SalesTable

Orderid

Region

Total

1

A

100.00

2

C

80.00

3

A

130.00

4

B

90.00

5

B

100.00

6

C

120.00

7

A

90.00

8

C

90.00

9

B

80.00

SQLStatement:

Select   *   From Sales As S
Inner   Join  
( Select   AVG (Total) As Avge, Region From Sales Group   By Region) avgtable
On S. Region = Avgtable. Region
Where Total > Avgtable. avge

 

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.