One . Group query
1. Use group by Group query
In the use of group by When a keyword , stay select The items that can be specified in the list are limited ,select Only the following items are allowed in the statement :
〉 Grouped Columns
〉 Returns a value expression for each group , For example, an aggregate function with a column name as a parameter
group by Let's just write a few examples :
example :
select courseID,avg(score) as Course average
from score
group by courseID
example :
select studentID as Student number ,courseID as Internal test ,avg(score) as Internal test average
from score
group by studentID,courseID
2. Use having Clause
where Clause can only filter data rows without grouping statistics , The filtering of grouped conditions must use having Clause
example :
select studentID as Student number ,courseID as Internal test ,avg(score) as Internal test average
from score
group by studentID,courseID
having avg(score)>60

stay select In the sentence ,where、group by、having The order of execution of clauses and statistical functions is as follows :
where Clause removes data that does not meet the search criteria from the data source ;group by Clause to collect rows of data into groups , The statistical function calculates statistics for each group ;having Clause removes groups of data rows that do not meet its group search criteria .

SQL Group query GroupBy More articles about

  1. SQL The use of group query and aggregate function

    Today we will do a query and statistics function , It was a bit difficult at first , I try to write statistics in the morning sql sentence , I found myself sql Group query and aggregation function are not well learned : In fact, that is group by Clause and several aggregation functions , It's easy to use statistics skillfully . In conclusion, today ...

  2. SQL Group query group by

    Be careful :select Fields after , Must either be included in group by in , Or included in having In the aggregate function after . 1. GROUP BY It's a group query , commonly GROUP BY It's used with aggregate functions group ...

  3. complex sql Group query ( pivot)

    There are years in a data table . month . Japan . amount of money . Payment method, etc , And now I want to write sql sentence , The amount of each payment method for each day ( How much is the payment method ) Line up in the same line , Finally, add a subtotal of all the current payment methods . Here's the picture : primary sql ...

  4. SQL Group query group by

    select * from emp --deptno It's the department number -- Output the number of each department and The average wage in the Department --group by deptno; adopt deptno grouping select deptno, ...

  5. SQL Group query the first few data of each group

    /* The first way to do it , Inefficiency and mistakes */ DECLARE @DD DATETIME SET @DD = GETDATE() SELECT a.GoodsID , a.Account , a.LastU ...

  6. SQL Group query

      Divide data into logical groups , So that the data of each group can be manipulated , Aggregate each group . 1. Create grouping The group is in SELECT Of the statement GROUP BY Clause Example :         SELECT vendId ...

  7. sql Group query the number of pass or fail

    select score as Category ,count(*) as The number of from (select case when fen>=60 then ' pass ' else ' fail, ' end as scor ...

  8. mysql Series nine 、mysql Statement execution process and operation principle ( The principle of group query and association query )

    One . Background introduction Know a sql Statement execution , Understand what part of it has done , Better for sql To optimize , Because you know every connection to it .where. grouping . How subqueries work , What did you do , It's unreasonable to know how to write . roughly ...

  9. Group by Group query actual combat

    Practical experience , Because I'm at work sharing my bike , Our bicycle management module , You can check the bicycle number , But the bike number is not set up unique( Unique constraints ), Therefore, it increases the risk of repeated bicycle numbers , But in general , Bicycle numbers don't repeat , because ...

Random recommendation

  1. Personal websites are good for xss Cross-site scripting attacks ( The focus is on the rich text editor ) and sql Inject attack protection

    Yesterday, this blog received xss Cross-site script injection attacks ,3 We're going to take... In minutes -- In fact, the attacker's attack tactics are very simple , No technical content . I can only sigh that I didn't take precautions before . Here are some records left in the database . In the end, the man created a script with an infinite loop pop-up box , It is estimated that ...

  2. Star capital's rapid development engine :IT It's productivity

    Xinghao capital was founded in 2010 year , It's a private equity fund . Development management . It is a comprehensive management company with three business areas of Commerce and modern service industry , Focus on investing in China's first . High growth . Innovative urban complex with high rate of return . Young star capital has its own unique business in business investment ...

  3. 【krpano】 Browse the like plugin ( Source code + Introduce + preview )

    brief introduction In recent days, I have studied how to krpano On the basis of panorama, it can record the number of views and likes , Wrote a plug-in , Easy to use . The screenshot of the effect is as follows : Whenever a user opens the panoramic page , The number of views will increase automatically 1: Users can take the initiative to click the like button , Click on ...

  4. Hibernate The mapping of association relations

    Relationship between entities There are three relationships between entities One to many : A user , Generate multiple orders , Each order can only belong to one user Principle of building table : Create a field on more than one side , As a foreign key , A primary key that points to a party of one Many to many : A student can choose multiple courses , A lesson ...

  5. 20155229- Fu Yuhan - Analytical self skills extend to c Language learning situation

    My little skills I remember the dance I performed in kindergarten , I also remember learning from watercolor to sketch , I still remember the provincial piano awards won by primary school and junior high school . Dance ends in grade one , Painting ends in the third grade , There was a break in the ten years from preschool to senior one . 03 year -04 That winter in ...

  6. 【 Reprint 】javascript And C# The grammatical difference between

    Because bloggers are not allowed to reprint , I just put links here

  7. 53. Maximum Subarray【leetcode】

    53. Maximum Subarray[leetcode] Find the contiguous subarray within an array (containing at least one ...

  8. elementary analysis python in socketserver Module USES

    Although it's useful python It's very convenient to write a simple network program , But it's better to use a ready-made framework for a more complex network program , So you can focus on the logic of things , Instead of the details of the socket .Socketserver Module simplifies the preparation of network service program , meanwhile so ...

  9. android Latest update method

    Use SDK Manager There was a problem updating Failed to fetch URL ...

  10. CF396C On Changing Tree

    CF396C On Changing Tree Give a tree with \(1\) A tree for roots , At the beginning, the weight of all points is \(0\) Yes \(m\) operations , Divided into two \(1\ u\ x\ k\) To give to \(u\) Of ...