SOQL 기본문법(05)

GROUP BY

Mon, May 27, 2019 written by Yong-Jin

GROUP BY

  SELECT LeadSource, COUNT(Name)
    FROM Lead
GROUP BY LeadSource

  SELECT LeadSource
    FROM Lead
GROUP BY LeadSource
 
GROUP BY 사용시 고려 사항
  1. 일부 오브젝트 필드에는 그룹화를 지원하지 않는 필드 유형이 있습니다.
  2. 쿼리가 LIMIT 절과 집계 된 함수를 사용하는 경우 GROUP BY 절을 사용해야합니다.
  3. GROUP을 사용하는 쿼리에서 __r 구문을 사용하는 자식 관계 식을 사용할 수 없습니다.
 
GROUP BY와 함께 별칭 사용하기

연관된 필드 바로 뒤에 별명을 지정하십시오. 예를 들어, 다음 쿼리에는 Name 필드의 n과 MAX (Amount) 집계 필드의 max라는 두 개의 별칭이 포함되어 있습니다.

  SELECT Name n, MAX(Amount) max
    FROM Opportunity
GROUP BY Name

다음 예제에서 MAX (Amount)는 expr0의 암시 적 별칭을 가지며 MIN (Amount)은 expr1의 암시 적 별칭을가집니다.

  SELECT Name, MAX(Amount), MIN(Amount)
    FROM Opportunity
GROUP BY Name

다음 쿼리에서 MIN (Amount)은 min이라는 명시적인 별칭을가집니다.
MAX (Amount)에는 expr0의 암시 적 별칭이 있고 SUM (Amount)에는 expr1의 암시 적 별칭이 있습니다.

  SELECT Name, MAX(Amount), MIN(Amount) min, SUM(Amount)
    FROM Opportunity
GROUP BY Name
 

GROUP BY ROLLUP

         SELECT LeadSource, COUNT(Name) cnt
           FROM Lead
GROUP BY ROLLUP (LeadSource)
LeadSource cnt
Web 7
Phone Inquiry 4
Partner Referral 4
Purchased List 7
Null 22
 
         SELECT Status, LeadSource, COUNT(Name) cnt
           FROM Lead
GROUP BY ROLLUP (Status, LeadSource)
Status LeadSource cnt
Open - Not Contacted Web 1
Open - Not Contacted Phone Inquiry 1
Open - Not Contacted Purchased List 1
Open - Not Contacted null 3
Working - Contacted Web 4
Working - Contacted Phone Inquiry 1
Working - Contacted Partner Referral 3
Working - Contacted Purchased List 4
Working - Contacted null 12
Closed - Converted Web 1
Closed - Converted Phone Inquiry 1
Closed - Converted Purchased List 1
Closed - Converted null 3
Closed - Not Converted Web 1
Closed - Not Converted Phone Inquiry 1
Closed - Not Converted Partner Referral 1
Closed - Not Converted Purchased List 1
Closed - Not Converted null 4
null null 22
 
         SELECT LeadSource, Rating,
                GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
                COUNT(Name) cnt
           FROM Lead
GROUP BY ROLLUP (LeadSource, Rating)

grpRating = 1은 결과가 등급 필드로 그룹화되었음을 나타냅니다.

LeadSource Rating grpLS grpRating cnt
Web null 0 0 5
Web Hot 0 0 1
Web Warm 0 0 1
Web null 0 1 7
Phone Inquiry null 0 0 4
Phone Inquiry null 0 1 4
Partner Referral null 0 0 4
Partner Referral null 0 1 4
Purchased List null 0 0 7
Purchased List null 0 1 7
null null 1 1 22
 
       SELECT Type, BillingCountry,
              GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,
              COUNT(id) accts
         FROM Account
GROUP BY CUBE (Type, BillingCountry)
     ORDER BY GROUPING(Type), GROUPING(BillingCountry)
Type BillingCountry grpType grpCty accts
Customer - Direct null 0 0 6
Customer - Channel USA 0 0 1
Customer - Channel null 0 0 2
Customer - Direct USA 0 0 1
Customer - Channel France 0 0 1
null USA 0 0 1
Customer - Channel null 0 1 4
Customer - Direct null 0 1 7
null null 0 1 1
null France 1 0 1
null USA 1 0 3
null null 1 0 8
null null 1 1 12

이러한 쿼리 결과를 사용하여 결과에 대한 크로스 테이블 보고서를 제공 할 수 있습니다.

Type/BillingCountry USA France null Total
Customer - Direct 1 0 6 7
Customer - Channel 1 1 2 4
null 1 0 0 1
Total - Direct 3 1 8 12
SOQL