The GROUP BY clause is used by the SELECT statement to arrange the same values rows in groups. These clauses often use aggregated functions such as AVG(), SUM(), COUNT(), etc. to group the results of one or more columns. The GROUP BY () is followed by the WHERE clause.
- Syntax for GROUP BY –
SELECT column1,column2,column3,...,columnN FROM table_name
WHERE condition
GROUP BY column1,column2,...
ORDER BY column1,column2,...;
Demo Table –
Product Table
| Category | City | Country | Customer_ID | Customer_name |
| Office Supplies | Chicago | United States | SM-20950 | Suzanne McNair |
| Technology | New York City | United States | AH-10465 | Amy Hunt |
| Office Supplies | New York City | United States | AH-10465 | Amy Hunt |
| Office Supplies | Dover | United States | EP-13915 | Emily Phan |
| Office Supplies | Dover | United States | EP-13915 | Emily Phan |
| Technology | Aurora | United States | TP-21565 | Tracy Poddar |
| Office Supplies | Aurora | United States | TP-21565 | Tracy Poddar |
| Office Supplies | Long Beach | United States | AR-10825 | Anthony Rawles |
| Office Supplies | Pasadena | United States | HA-14920 | Helen Andreada |
| Furniture | Pasadena | United States | HA-14920 | Helen Andreada |
Example – Consider the Product table of the Customer database where we want the result as a Count of Customers and their respective cities, and the result should be grouped by the “City”.
SELECT
COUNT([Customer Name]) as Total_CountOfCustomer,City
FROM [dbo].[Products]
GROUP BY City;
The output after implementing the above GROUP BY –
| Total_CountOfCustomer | City |
| 2 | Aurora |
| 1 | Long Beach |
| 2 | New York City |
| 1 | Chicago |
| 2 | Pasadena |
| 2 | Dover |
But, let’s order the Total_CountOfCustomers in descending order –
SELECT
COUNT([Customer Name]) as Total_CountOfCustomer,City
FROM [dbo].[Products]
GROUP BY City ORDER BY Total_CountOfCustomer;
The output after implementing the above ORDER BY –
| Total_CountOfCustomer | City |
| 2 | Aurora |
| 2 | Dover |
| 2 | New York City |
| 2 | Pasadena |
| 1 | Chicago |
| 1 | Long Beach |
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz
