Project 5 - 2 Use ORDER BY and GROUP BY

Project 52 Use ORDER BY and GROUP BY

The ORDER BY and GROUP BY clauses of the SELECT command are used to manipulate your data output into more clearly understandable formats specifically focused for the intended audience. This project will give you practical experience with these clauses, as well as with using the HAVING clause in conjunction with GROUP BY to further distill your datas presentation.

Step by Step

Use ORDER BY to list all the designs in duck_sales arranged by their category.

 SELECT design_name, design_category FROM duck_sales ORDER BY design_category;

Use two ORDER BY fields to display the design names within each category in a sorted order.

 SELECT design_name, design_category FROM duck_sales ORDER BY design_category, design_name;

Use the following GROUP BY clause to list only the unique states in the cust_state column of the duck_cust table.
```
 SELECT cust_state FROM duck_cust GROUP BY cust_state; 
```
Use the following GROUP BY clause to list only the unique cities and states in the cust_city and cust_state columns of the duck_cust table.
```
 SELECT cust_city, cust_state FROM duck_cust GROUP BY cust_state; 
```
This command results in a list of states identical to the results of the command in step 3; however, in the case of CA, there is more than one city in the table, so it does not give a complete listing of the cities. Grouping by state, MySQL displays only the city from the first CA state record it finds.
Change the GROUP BY field to cust_city, to list all the unique cities and their states, including both cities from CA.
```
 SELECT cust_city, cust_state FROM duck_cust GROUP BY cust_city; 
```
Using the following SELECT command with WHERE, GROUP BY and HAVING clauses, list the design_name or names in the Literary design_category that have winter_sales over 700, in order of most sales to least sales.
```
 SELECT design_name, design_category, winter_sales FROM duck_sales WHERE design_category="Literary" GROUP BY winter_sales DESC HAVING winter_sales>700; 
```

Project Summary

In this project, you used GROUP BY and ORDER BY clauses to control the output of your SELECT commands. You specified both single and multiple fields, and also used the HAVING clause to further refine the output of your GROUP BY clause.

Although the syntax for each of these clauses is simple, the ways in which they can be utilized can result in increasingly complex manipulations. Using them, you can produce specific reports of a tables contents and see how its fields relate to each other.