Kiến thức

SQL MAX() with HAVING, WHERE, IN-w3resource

SQL MAX() with HAVING

Last update on February 26 2020 08:07:42 (UTC/GMT +8 hours)

Bạn đang xem: SQL MAX() with HAVING, WHERE, IN-w3resource

MAX() function with Having

In this article we have discussed how SQL HAVING CLAUSE can be used along with the SQL MAX() to find the maximum value of a column over each group. The SQL HAVING CLAUSE is reserved for aggregate function.

The usage of WHERE clause along with SQL MAX() have also described in this page.

The SQL IN OPERATOR which checks a value within a set of values and retrieve the rows from the table can also be used with MAX function.

Example :

Sample table :customer

To get data of ‘cust_city’, ‘cust_country’ and maximum ‘outstanding_amt’ from the customer table with following conditions –

1. the combination of ‘cust_country’ and ‘cust_city’ should make a group’

the following SQL statement can be used :

SELECT cust_city, cust_country, MAX(outstanding_amt) FROM customer GROUP BY cust_country, cust_city HAVING MAX(outstanding_amt)>10000; 

Output :

CUST_CITY CUST_COUNTRY MAX(OUTSTANDING_AMT) ----------------------------------- -------------------- -------------------- Bangalore India 12000 Chennai India 11000 London UK 11000 Mumbai India 12000 Torento Canada 11000 

Pictorial Presentation :

SQL MAX() function WITH HAVING exampleSQL MAX() function WITH HAVING example

SQL MAX() in where

Sample table : customer

To get data of ‘cust_country’ and maximum ‘outstanding_amt’ from the ‘customer’ table with following conditions –

1. ‘cust_country’ should be formatted in a group,

2. ‘grade’ must be 2,

the following SQL statement can be used :

SELECT cust_country,MAX(outstanding_amt) FROM customer WHERE grade=2 GROUP BY cust_country; 

Output :

CUST_COUNTRY MAX(OUTSTANDING_AMT) -------------------- -------------------- USA 6000 India 12000 Australia 5000 Canada 8000 UK 6000 

Pictorial Presentation :

SQL MAX() function with WHERE exampleSQL MAX() function with WHERE example

SQL MAX() with IN operator

Sample table : customer

To get data of ‘opening_amt’ and maximum of ‘outstanding_amt’ from the ‘customer’ table with following conditions –

1. the ‘opening_amt’ should come in a group,

2. the ‘opening_amt’ should be 3000 or 8000 or 10000,

the following SQL statement can be used :

SELECT opening_amt, MAX (outstanding_amt) FROM customer GROUP BY opening_amt HAVING opening_amt IN (3000,8000,10000); 

Output :

OPENING_AMT MAX(OUTSTANDING_AMT) ----------- -------------------- 10000 11000 3000 6000 8000 12000 

Pictorial Presentation :

SQL MAX() function with IN operator exampleSQL MAX() function with IN operator example

SQL MAX() with HAVING and IN

Sample table : orders

To get data of ‘agent_code’, number of agents as ‘count(agent_code)’ and the maximum ‘ord_amount’ from the ‘orders’ table with following conditions –

1. ‘agent_code’ should be formatted in a group,

2. maximum ‘ord_amount’ should be 500 or 800 or 2000,

the following SQL statement can be used :

SELECT agent_code,COUNT(agent_code),MAX(ord_amount) FROM orders GROUP BY agent_code HAVING MAX(ord_amount) IN(500,800,2000); 

Output :

AGENT_CODE COUNT(AGENT_CODE) MAX(ORD_AMOUNT) ---------- ----------------- --------------- A007 2 2000 A009 1 500 A012 2 2000 A001 1 800 

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here

is a slide presentation of all aggregate functions.

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button