I. Use of GROUP BY in Oracle's SQL (relationship to aggregate functions)

SELECT R.industry_1,r.industry_2,r.agent_id,r.agent_name,
COUNT (DISTINCT r.customer_name_a) total data,
COUNT (DISTINCT case is r.ifhs= ' Y ' then r.customer_name_a END) amount of customs data,
COUNT (DISTINCT case is r.ifgjh= ' Y ' then r.customer_name_a END) Canton Fair Data volume,
COUNT (DISTINCT case when r.zlly like '% a class-% ' then R.customer_name_a END) old customer data volume
From Rep_com_allinfo R WHERE r.outbound= ' 10000115 '
GROUP by R.industry_1,r.industry_2,r.agent_id,r.agent_name

Note: 10000115 means 2010 Beauty Project group
And most importantly, the count used here is the aggregate function, so be sure to group by the group by statement at the end.

Detailed analysis:

use of GROUP by

Before we introduce the group BY and HAVING clause, we must first talk about a special function in the SQL language: aggregate functions such as SUM, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.

SELECT SUM (population) from BBC

The SUM function here is on the population field of all returned records, and the result is that the query returns only one result, that is, the total population of all countries.

By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group. When you specify group by regions, a set of data that belongs to the same region will return only one row of values, that is, all fields except region (regions) in the table can only be returned with a value after the SUM, count, and other aggregate function operations.

The HAVING clause allows us to filter groups of data, where clauses filter records before aggregation. That is, it works before the GROUP BY clause and the HAVING clause.
The HAVING clause filters the group records after aggregation.

Let's take a concrete example to understand the group BY and having clauses, as well as the BBC table introduced in section Iii.

SQL instance:

First, show the total population and area of each region:

SELECT region, sum (population), sum (area) From BBC GROUP by region

The return record is divided into groups with region first, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with an aggregate function.

Second, show the total population and area of each area. Only those areas with more than 1000000 area are shown.

SELECT region, sum (population), sum (area) From BBC GROUP by region Have SUM (area) >1000000

Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table.

Instead, the HAVING clause allows us to filter groups of data after the group.

=================================================================================

Group BY is a grouped query, and the general group by IS used with aggregate functions, you can think about

You have used group by to press ITEM. Itemnum This field group, the other field content is different, become a pair of more and change how to display it, such as the following

A B
1 ABC
1 BCD
1 ASDFG

Select A b from table group by A
You said it was a result of finding out.

A B
Abc
1 BCD
Asdfg

The 3 on the right becomes one, so you need to use aggregate functions, such as

Select A,count (B) Quantity from table group by A
So the result is
A Quantity
1 3

Group by has a principle that all columns following the SELECT, which do not use aggregate functions, must appear behind group by.

Transferred from: http://fangchengmi123.blog.163.com/blog/static/106833456201022461420632/

I. Use of GROUP BY in Oracle's SQL (relationship to aggregate functions)