Unions

00:00

Hi. Welcome back to Module six. This is less than 6.7 unions, and this lesson. We're going to review the applications off unions in the sequel language.

00:09

So to review unions are an easy way to stack multiple results sets

00:14

something to keep in mind is that all combined sets must have the following in common. They must have the same number of columns and the stack columns must be of the same data types. Examples include combining two tables of similar data into one virtual table via a derived table or common table expression.

00:33

Another thing you might do with unions is create a total line for a report and this example below. We have a very, very simple, select statement. Select one union Select two.

00:43

Now, if we wanted to, we could add in another union with a union keyword than and then a select three below that and that would work Now with that said, let's go ahead and head over through the development environment and take a look at the Union.

00:58

Welcome back to the development environment for this. Listen, we're using the axiomatic and database, and we're gonna focus on the PM Tran table.

01:06

If we take a look at that table and see it's filled with a bunch of, ah, training data or sample data,

01:11

now we look at the properties will see that this table has a large amount of columns. In fact, 66 columns to be exact.

01:19

And however the columns we're going to focus on will be the reference number, which ties back to a Projects Last contract number

01:26

and the amount column, which which ties back to an amount associated with that project for some

01:34

reason. For example, this line deals with installed latest software closed case

01:41

so we could tell this the amount associated with this line had something to do with installing software. We're not gonna worry about the descriptions at all. We're just gonna get back the amounts and then use a union to get created total line.

01:53

So let's go ahead and get started with that

01:56

select star rahm PM Tran.

02:02

I'm gonna group by

02:05

reference number.

02:07

And of course, we need to make sure our selection columns match that so reference number,

02:12

comma, some

02:15

amount. Let's go ahead and run that.

02:19

Okay,

02:21

now, this isn't the whole truth. When you look at these results. If we select zero column or the first record that has a zero, we'll see the actual values 0.0 So four decimal places for all our results, even though some of them look like they do not have those four decimal places.

02:38

So I want to go ahead and add a unique requirement. I'm going to say that I don't want anything that came back with a decimal of zero or a decimal 00.0

02:52

How would I How would I do that?

02:53

Well, one way that I might do that is, I might go. Having remember, we have to use the having to filter on

03:01

aggregate functions. So having I'm gonna cast

03:08

the some of the amount

03:14

as char. So this will take that amount and turn it into a string.

03:19

And then I'll just checked that that string is not like

03:24

the wild card, which is the percent sign 0.0

03:30

Now they're on this.

03:32

I now only have results that had some amount of sense associated with it, so no

03:38

0.0 are included.

03:44

Now let's say we want to get the total line. Well, we might do something like this, but this is going to create a bit of a problem

03:51

because of that unique requirement we have.

03:53

And after you've been in the field for a while, you will get these kind of requirements that are,

03:59

You might say odd to look at it first.

04:02

So if we take away the group by

04:06

and we just do a some Well, actually, we need to make sure the columns match. So I'm gonna change this to the changes to the word total.

04:16

And we run this.

04:17

It seems to have worked.

04:20

If you go to the bottom, we have a total line.

04:24

But if we add these all up Andy Beaver,

04:29

we will see

04:30

the best. Definitely not the sum. So let's break out the calculator.

04:38

It was a way to add a function.

04:40

Okay, right. Click. Add function some.

04:44

Okay, So 96 4 to 7.0 to

04:47

That's a very different from the number we're reporting here. So we know that this is wrong

04:53

and the reason it is wrong is because this exclusion

04:57

is not working the same way because we've changed the grouping.

05:00

So how might we handle this? Well, I'm gonna tell you the easiest way is to use one of the tools we discuss before. And that's a table expression. So we'll say with

05:12

I'm just gonna call it Query as.

05:18

And I'm gonna put that original query

05:23

in that table expression

05:28

and then I'm going to select star from Query,

05:31

Go ahead and run that.

05:33

So I've lost my total line

05:38

and then I wouldn't do a union.

05:41

I'm gonna select

05:44

total.

05:46

Remember, the columns have to line up. They have to be the same type, so that word total will be a string. So line up with the reference number

05:54

and then we need to some

05:58

the amount. Let's go in and change this to the word as amount,

06:04

some the amount.

06:06

And let's go ahead and run that

06:15

we, of course, need to finish. The Korean got ahead of myself

06:18

from query

06:23

and let's see what we got here.

06:26

So I got a total.

06:29

And if I highlight all these, I'll see that some is

06:32

the same. Is that total? So now I'm confident that I'm getting the correct results on that total line

06:38

and there you have it. We've written a query

06:41

with a union. They gives us a very, very simple report with respect to the column count. A lot of times these reports will have you know, a few more columns on them.

06:49

And then we use union to add a total line to the bottom of that query.

06:56

And we reached back into our tool set and used a table expression to make it easier and more intuitive to just union those results together and not have to deal with the different groupings, as

07:08

as we would have if we didn't have access to table expressions.

07:13

So that completes this lesson. Let's go ahead and head over to the summary,

07:16

and that brings us to the 6.7 summary and this lesson. We reviewed the applications of Union, and we created a union with amounts and a total line as an example