9 hours 41 minutes
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.
So to review unions are an easy way to stack multiple results sets
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.
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.
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.
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.
If we take a look at that table and see it's filled with a bunch of, ah, training data or sample data,
now we look at the properties will see that this table has a large amount of columns. In fact, 66 columns to be exact.
And however the columns we're going to focus on will be the reference number, which ties back to a Projects Last contract number
and the amount column, which which ties back to an amount associated with that project for some
reason. For example, this line deals with installed latest software closed case
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.
So let's go ahead and get started with that
select star rahm PM Tran.
I'm gonna group by
And of course, we need to make sure our selection columns match that so reference number,
amount. Let's go ahead and run that.
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.
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
How would I How would I do that?
Well, one way that I might do that is, I might go. Having remember, we have to use the having to filter on
aggregate functions. So having I'm gonna cast
the some of the amount
as char. So this will take that amount and turn it into a string.
And then I'll just checked that that string is not like
the wild card, which is the percent sign 0.0
Now they're on this.
I now only have results that had some amount of sense associated with it, so no
0.0 are included.
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
because of that unique requirement we have.
And after you've been in the field for a while, you will get these kind of requirements that are,
You might say odd to look at it first.
So if we take away the group by
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.
And we run this.
It seems to have worked.
If you go to the bottom, we have a total line.
But if we add these all up Andy Beaver,
we will see
the best. Definitely not the sum. So let's break out the calculator.
It was a way to add a function.
Okay, right. Click. Add function some.
Okay, So 96 4 to 7.0 to
That's a very different from the number we're reporting here. So we know that this is wrong
and the reason it is wrong is because this exclusion
is not working the same way because we've changed the grouping.
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
I'm just gonna call it Query as.
And I'm gonna put that original query
in that table expression
and then I'm going to select star from Query,
Go ahead and run that.
So I've lost my total line
and then I wouldn't do a union.
I'm gonna select
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
and then we need to some
the amount. Let's go in and change this to the word as amount,
some the amount.
And let's go ahead and run that
we, of course, need to finish. The Korean got ahead of myself
and let's see what we got here.
So I got a total.
And if I highlight all these, I'll see that some is
the same. Is that total? So now I'm confident that I'm getting the correct results on that total line
and there you have it. We've written a query
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.
And then we use union to add a total line to the bottom of that query.
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
as we would have if we didn't have access to table expressions.
So that completes this lesson. Let's go ahead and head over to the summary,
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
that completes this lesson, and I hope to see you in the next Thank you