Using a Union to Add a Grand Total

It is possible to add a grand total line to a query in a couple of ways: using the WITH ROLLUP clause and using a UNION. I will cover the latter here and the former in another post.

Example
Show the total quantity ordered and the total cost for the orders in the bookstore database. Add to this a total line showing the total quantities and costs for the entire orderlines table. In other words present the detail and the summary.

Approach
Take the subtotal as you normally would using a group by with aggregate functions in the select list. Then union the results with another select without a group by clause. This latter select will need an additional column to make it union compatible with the first select. The number of columns has to be the same. You can use a null which is what the rollup does.

Script
use bookstore; -- MySQL and SQL Server
select order_numb
, sum(quantity) as "Quantity"
, sum(cost_line)as "Total Cost"
from orderlines
group by order_numb
union
select NULL, sum(quantity), sum(cost_line)
from orderlines;
Results

The following is part of the result set showing the total line with a null in the first column.

Blog Roll

I added a couple blogs to the blog list on this blog. The SQL half of the Oracle SQL & PL/SQL one seems to be sometimes based on SQL Server but it nevertheless features a handy index to all the SQL commands. The introductory portions on DDL/DCL/DML need work, though. And then I added another blog that features extracts from Joe Celko's writings and posts. His comments can be colorful but are worth reading as he is one of the leading SQL authors out there.

I'll update the list overtime. My current intention is to confine the list to blogs only and just those here on blogger.

Bookstore database

Below is a screenshot from the Microsoft Access relationship designer of the bookstore database. This database is one of the main ones used in our classes. It comes from SQL Clearly Explained by Jan Harrington. You can download the scripts to build this database at SQL Scripts. There are versions for both Oracle and SQL Server.

Adobe

Just a quick note. We do have a couple of instructors that are very skilled with the Adobe product set. I just wanted to pass this information along as not many people are aware of it. We teach the classes by going to your location. We can teach most of the products such as PhotoShop, Illustrator, InDesign, etc.

Combination Queries

There is a set of queries sometimes referred to as combination queries. There are three set operators used to create them...

• UNION
• INTERSECT
• EXCEPT

We looked at the Union in our basic class. Below we will present the other two. Each is based on the bookstore database which can be downloaded here.

INTERSECT
The intersect operator retrieves all unique rows found in both tables.

Example
The following query will find all cities in common between the sources and customers tables.

Script
use bookstore -- MySQL and SQL Server
select customer_city, customer_state, customer_zip
from customers
intersect
select source_city, source_state, source_zip
from sources;

EXCEPT
The except operator will retrieve all rows found in the first table but not in the second.

Example
Find all cities where the source is located in a city that does not have a customer.

Script
use bookstore -- MySQL and SQL Server
select source_city, source_state, source_zip
from sources
except
select customer_city, customer_state, customer_zip
from customers;

Calculating the Mode of a Dataset

Scenario
SQL script to calculate the mode of a dataset.

Approach
Determine the frequency of occurrence of each value. Sort from largest to smallest. Select the first frequency so long as there is more than one occurrence. Allow for ties in case of bimodal distributions. The script for the parts table can be found in the post for the median.
Script
USE parts
SELECT TOP 1 WITH TIES COUNT(*) occurrences, part_wgt mode
FROM parts
GROUP BY part_wgt
HAVING count(*) > 1
ORDER BY COUNT(*) DESC;

Result

Resources

PowerPoint Presentations of our SQL Classes can be found on SlideShare.

Script files that can be downloaded and run can be found on Box.net. The script files are arranged both by database and by course.

Recirculating Scripts

I often advise students to put the following lines of code at the top of their SQL Server scripts that build and load databases.

use master
drop database yourdatabase;
go
create database yourdatabase;
go
use yourdatabase
--insert your code here to create and load tables
This will first make sure you are in the master database and don't get a database in use error when doing the drop. Then drop the database and recreate it. The GO commands separate the script into batches so if the database doesn't already exist the next batch, the create, will run, even though the drop generated an error. This code works fine but there is a more elegant way.

use master
if db_id('yourdatabase') is not null
drop database yourdatabase;
create database yourdatabase;
use yourdatabase
--insert your code here to create and load the tables
This method tests for the existence of the database first then drops it. Either way is fine. Both make it easy to go in and correct some of the code and then just re-run the script.

---Dan

T-SQL Fundamentals Book Review

I just finished an excellent SQL book...

Ben-Gan, I. (2009). Microsoft SQL Server 2008 T-SQL Fundamentals. Redmond: Microsoft Press.

It is one of the best SQL books I have read. It is written in  a very straightforward style. There are 10 chapters covering all the usual topics from table creation up through triggers and stored procedures. What I especially liked about this book was that it goes into some more advanced topics. It discusses pivot and unpivot, common table expressions, the newer with clause and window functions for example. The author also has some good example exercises for testing transactions. It you are looking for a straightfoward presentation of these more advanced topics, this is your book.

Our intermediate level SQL class will feature some material drawn from this book, although adapted to our sample classroom databases.

SQL Script to calculate the median

Business case
Below is a script that will calculate the median of a dataset. The median is the middle value in an ordered dataset. If the dataset has an even number of values, the median is the average of the innermost two.

Approach
This script works by taking the union of two select top values queries. If there are an odd number of records the same values are returned twice which is fine. The values returned by the union are then averaged. Note the use of derived tables. This example uses the famous parts table from Chris Date. The idea for this script came from a tek-tips SQL forum.

Script
use master
drop database parts;
go
create database parts;
go
use parts
-- create Date's famous parts table
CREATE TABLE Parts (
part_nbr VARCHAR(5) NOT NULL PRIMARY KEY,
part_name VARCHAR(50) NOT NULL,
part_color VARCHAR(50) NOT NULL ,
part_wgt INTEGER NOT NULL ,
city_name VARCHAR(50) NOT NULL);
-- load data (experiment with different numbers of rows, etc.)
INSERT INTO Parts (part_nbr, part_name, part_color, part_wgt, city_name)
VALUES ('p1', 'Nut', 'Red', 12, 'London'),
('p2', 'Bolt', 'Green', 17, 'Paris'),
('p3', 'Cam', 'Blue', 12, 'Paris'),
('p4', 'Screw', 'Red', 14, 'London'),
('p5', 'Cam', 'Blue', 12, 'Paris'),
('p6', 'Cog', 'Red', 19, 'London');
use parts
-- display data
select *from parts
order by part_wgt;

 -- calculate the median
select avg(wgt) as median
from(select max(part_wgt) as wgt
from (select top 50 percent *
from parts
order by part_wgt asc) a
union
select min(part_wgt)
from (select top 50 percent *
from parts
order by part_wgt desc) d) u;

Results

Welcome

Hello All -

This blog will be used as a quick way to post our new SQL Scripts and such. Many of these scripts will eventually be rolled into tutorials and/or courses, perhaps here. Over time, we will also post scripts applicable to existing classes taught at Orange Coast Database Associates or at the University. Most scripts are cross platform. We teach classes in both the Oracle and SQL Server environments, and occaisionally MySQL and Access Jet SQL.

---Dan