Count, group by, having (SQL)

--- Price statistics for all books

Select sum (price) total price, AVG (price) average price, max (price) highest price, min (price) lowest price

From titles

--- Count records of where conditions
--- Business-type book price statistics
Select sum (price) total price, AVG (price) average price, max (price) highest price, min (price) lowest price
From titles where type = 'business'

-- Count
-- Returns the author from several states.
Select count (distinct state) from authors

Select count (au_id) from authors
-- Return the number of records in the table
Select count (*) from authors

Select * from titles

-- Number of records of type
Select count (distinct type) from titles

Select count (title_id) from titles

-- Group
-- Returns the statistics of books of different categories.
Select Type, sum (price) total price, AVG (price) average price, max (price) highest price, min (price) lowest price,
Count (*) number of entries from titles group by type

-- Return the number of books published by each publisher and sort them in descending order)
Select * from titles
Select pub_id, count (*) quantity from titles group by pub_id order by quantity DESC

--- Number of books published by 1389 publishers
Select * from titles
Select count (*) quantity from titles where pub_id = 1389

-- Group statistics on type and pub_id
Select count (*) quantity, type, pub_id from titles group by type, pub_id
Order by quantity DESC

-- Having filter group
-- Return the average price of the Category> 15 books
Select AVG (price) average price, type from titles group by type having AVG (price)> 15
-- Note: calculate the average value first, and then the average price> 15 records.

Select AVG (price) average price, type from titles
Where price> 15 group by type
-- Note: first seek the record of price> 15, then seek the average price of price> 15 according to the category.

-- Returns the category of books with an average price between 13 and 18.
Select AVG (price) average price, type from titles group by type
Having AVG (price) between 13 and 18

-- Return the number of published books> = press number of 6
Select * from titles

Select count (*) quantity, pub_id from titles
Group by pub_id having count (*)> = 6

-- Returns the name of the State with the most authors.
Select * from authors

Select top 1 State, count (*) quantity from authors group by state
Order by count (*) DESC

-- Return the statistical information of business and mod_cook.
Select * from titles

Select Type, sum (price) total price, AVG (price) average price, max (price) highest price, min (price) lowest price
From titles where type in ('business', 'mod _ cook') group by type
-- Note: first select books of the business and mod_cook categories based on the where conditions, and then make statistics.

Select Type, sum (price) total price, AVG (price) average price, max (price) highest price, min (price) lowest price
From titles group by type having type in ('business', 'mod _ cook ')
-- Note: collect statistics first, and then select books of the business and mod_cook categories based on the where conditions.

 

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

For example, query select fsno, AVG (fgrade) as favggrade from tstudent group by fsno having AVG (fgrade)> 60 is equivalent to select * from (select fsno, AVG (fgrade) as favggrade from tstudent group by fsno) as twhere favggrade> 60: having performance is higher, code is more concise about on: Join filtering that can be used for conditional filtering where: normal conditional filtering having: Conditional filtering After grouping

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

If a column does not appear in the group by clause, the column cannot appear directly after select or having clause. An aggregate function must be added. for example, select Min (AGE), Gender from employee table group by gender-if the age does not appear in the group by clause, you must add an aggregate function to write it behind select.