메뉴 닫기

SQL GROUP BY Clause

The SQL GROUP BY syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames

The general syntax with ORDER BY is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames
  5. ORDER BY columnnames

 


 

SQL GROUP BY Examples

Problem: List the number of customers in each country

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. GROUP BY Country

Results: 21 records.

Count Country
3 Argentina
2 Austria
2 Belgium
9 Brazil
3 Canada

 


Problem: List the number of customers in each country sorted high to low

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. GROUP BY Country
  4. ORDER BY COUNT(Id) DESC

Results: 21 records.

Count Country
13 USA
11 France
11 Germany
9 Brazil
7 UK

 


Problem: List the total amount ordered for each customer

  1. SELECT SUM(O.TotalPrice), C.FirstName, C.LastName
  2. FROM [Order] O JOIN Customer C
  3. ON O.CustomerId = C.Id
  4. GROUP BY C.FirstName, C.LastName
  5. ORDER BY SUM(O.TotalPrice) DESC

This query uses a JOIN with Customer to obtain customer names
Results: 89 records.

Sum FirstName LastName
117483.39 Horst Kloss
115673.39 Jose Pavarotti
113236.68 Roland Mendel
57317.39 Patricia McKenna
52245.90 Paula Wilson
34101.15 Mario Pontes
32555.55 Maria Larsson

8 Comments

  1. g what

    I feel that is among the such a lot vital info for me.
    And i am satisfied studying your article. But want to commentary on few common issues, The site style is perfect, the articles is
    truly great : D. Good job, cheers

  2. http://j.mp/3drY6lE

    Cbd oil that works 2020
    I have been browsing on-line greater than 3 hours these days, yet I never
    discovered any fascinating article like yours. It’s
    beautiful price sufficient for me. In my view, if
    all web owners and bloggers made excellent content material as you probably did,
    the web might be a lot more useful than ever before. best rated cbd oil http://j.mp/3drY6lE cbd oil
    that works 2020 http://j.mp/3drY6lE

  3. http://tinyurl.com/y9w2es92

    After checking out a number of the articles on your blog, I honestly appreciate your way of writing a blog.

    I saved as a favorite it to my bookmark webpage list and will be checking back in the near
    future. Please check out my web site too and let me know your opinion.

  4. adreamoftrains web hosting

    Hi, I believe your web site could possibly be having browser
    compatibility issues. Whenever I look at your web site
    in Safari, it looks fine but when opening in Internet Explorer, it’s got some overlapping issues.
    I just wanted to provide you with a quick heads up!
    Aside from that, wonderful site! adreamoftrains web
    hosting reviews

댓글 남기기

이메일은 공개되지 않습니다.