|
Custom Search
| |
|
Summarizing Data From Multiple Tables in MySQL - Part 2 This is the second part of the tutorial about summarizing data from multiple Tables in MySQL. In this tutorial, we will see how aggregated data can sometimes come from a temporary table. The temporary table is actually a SELECT query that can be used with a JOIN statement or directly used after the FROM clause. Practice #1: Join with a temporary table to get sales by year . This query shows how to get a subtotal for each order. The subtotal is calculated by another query (also called sub-query) and the result of the sub-query is used as a temporary table in a JOIN statement where it is joined with the Orders table on OrderID column. Then the subtotal is retrieved from the sub-query for each order in SELECT statement. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. select distinct a.ShippedDate,
Query result set - 296 rows returned: Practice #2. Sales by Category This query has two JOINs. Categories table joins with Products to get ProductName, and it also joins with a temporary table (as a result of a sub-query) to get Product Sales. For each category, we get the list of products sold and the total sales amount. Note that, the inner query for table c is used to get sales for each product on each order. It then joins with outer query on Product_ID. In the outer query, products are grouped for each category. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. select distinct a.CategoryID,
Here is the query result. 77 records returned. Practice #3. Employee Sales by Country This query is similar to the query above. It has two joins - a table and a temporary table. For each employee, get their sales amount, broken down by country name. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. select distinct a.Country,
Here is the query result. 296 records returned. Practice #4. Category Sales for 1997 This query doesn't use JOIN, but it has three joins in the sub-query (also is inner-query) after the FROM clause. When you put a select statement into a FROM clause, it becomes a subquery. The subquery returns a temporary table in database server's memory and then it is used by the outer query for further processing. This query shows sales figures by categories - mainly just aggregation with sub-query. The inner query aggregates to product level, and the outer query further aggregates the result set from inner-query to category level. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. select CategoryName, round(Sum(ProductSales), 2) as Category_Sales
Here is the query result. 8 records returned. Other tutorials in this category 1. Using Aggregate Functions and Group By Clause in MySQL |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |