An Intro to SQL Part 3 Aggregations and Sub-queries

By Charles McDowell

Comptia Certified Linux Networking Professional.

Cisco Certified Networking Technician.

Microsoft Technology Associate Database Administration

View fullsize

“It is only when they go wrong that machines remind you how powerful they are.”

— Clive James

Aggregations

In a nutshell, aggregations are used to convert many rows into a single row. the only thing that changes is the logic used on each column for its aggregation.

Let’s continue with our previous example, where we saw that there were repetitions in the results we got from our query. We know that Ellen Horton borrowed more than one book, but this is not really the best way to show this information. We can write another query :

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;

The result is :

First Name

Last Name

Number of books borrowed

Mike

Willis

1

Ellen

Horton

2

Most aggregations we do come with the `GROUP BY` statement but it’s not required.. What this does is convert the table otherwise returned by the query into groups of tables. Each group corresponds to a unique value (or group of values) of columns which we specify in the `GROUP BY` statement.

SELECT author, sum(stock)
FROM books
GROUP BY author;

The results are :

author

sum

Jane Doe

4

John Roe

6

Robert Spleen

3

Raiden Mcdonald

2

SubQueries

Sub queries are regular SQL queries, that are embedded inside larger queries.

There are 3 different types of sub-queries, based on what they return –

Two-dimensional table

These are queries that return more than one column.

SELECT *
FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
WHERE author=’Robin Sharma’;

Result :

author

sum

Jane Doe

4

One-dimensional array

Queries which return multiple rows of a single column, can be used as arrays, in addition to being used as Two-dimensional tables.

For example, lets say we want to get the titles and ids of all books written by an author, whose total stock of books is greater than 3.
We can break this down into 2 steps –

1. Get the list of authors with total stock of books greater than 3. Building on top of our previous example, we can write :

SELECT author
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE sum > 3;

·  

Which gives us :

author

Jane Doe

John Roe

Which can also be written as : [‘Jane Doe’, ‘John Roe’]

1. We then use this result in our next query :

SELECT title, bookid
FROM books
WHERE author IN (SELECT author
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
  WHERE sum > 3);

Which gives us :

title

bookid

Fake Book 1

2

Super Fake Book

3

Ultimate Fake Book

4

Single Values

Queries that have only one row , one column, can be treated as a constant value, and can be used anywhere a value is used. They can also be used like two dimensional tables, as well as an array containing 1 element.

As an example, let’s find out information about all books having stock above the average stock of books present.

The average stock can be queried using :

select avg(stock) from books;

Which gives us :

avg

3.000

Which can also be used as the scalar value 3.

So now, we can finally write our query :

SELECT *

FROM books

WHERE stock>(SELECT avg(stock) FROM books);

Which is equivalent to writing :

SELECT *

FROM books

WHERE stock>3.000

And which gives us :

bookid

title

author

published

stock

2

Ultimate Fake Book

John Roe

2012-01-04 00:00:00

6

Update

The syntax of UPDATE queries are semantically similar to read queries. The only difference however, is that instead of SELECTing columns from a bunch of rows, we SET those columns instead.

If we suddenly lost all of our books written by “John Roe”, we would like to update the stock to make it 0. For this we would write :

UPDATE books

SET stock=0

WHERE author='Jane Doe’;

WHERE is restricting the query to certain rows.  SET is choosing the column to update and seting the value.

Delete

A DELETE query removes an entire row, there is no such thing as specifying column names to delete. The below query deletes the entries from John Roe all together, we would write :

DELETE FROM books

WHERE author='John Roe';

Insert

We use Insert to add data to a table. The below query inputs the entire data of the “books” table :

INSERT INTO books (bookid,title,author,published,stock)

Values (69,’Fake Book vol 2’, ‘Jane Doe’, ‘2014-01-20)