Today I kept studying about TSQL (Having and Union command). I also finished the TSQL tutorial from Microsoft that Luis sent me.

HAVING clause

The first example shows a HAVING clause with an aggregate function. It groups the SalesOrderDetail rows by product ID and eliminates products whose average order quantities are less than five.

Having and Union command

This example shows a HAVING clause without aggregate functions.

Having and Union command

I spent some time trying to do new queries using the statement that I have learned, some were successful others fail. One important skill that I am improving is to understand not just the commands and queries, but how the database works… Actually, this is the most important point, because every customer has a different database, if you can understand easily the data model, it’ll be great! So, my tip is every opportunity you have to work with a database, try to understand how it works.

The following example shows a command using GROUP BY, HAVING, WHERE and ORDER BY clauses in one SELECT statement. After eliminating the products with prices over $25 and average order quantities under 5, it produces groups and summary values. It also organizes the results by ProductID.

Having and Union command

There is also the possibility to use SUM, AVG, and COUNT in a HAVING clause, it is amazing!

Having and Union command

Having and Union command

Having and Union command

 

UNION

I had a little trouble understanding this command and Luis helped me, it’s really simple. The union statement take the information of one table and put together with the information of another table, on the example I take information of the first 5 rows of the table and do the union with the rows between 10 and 15, so the result is:

Having and Union command

You can also do it with two different tables, but the structure of the tables need to be the same, or you select the same fields for both tables.

The next example of UNION, a table (dbo.Gloves) was created by the select return on the first block, then happens the SELECT for the UNION statement. It takes the information of the product model between the table ProductModel with a defined category and do the union with the table that was created (dbo.Gloves), the result is both information selected together.

Having and Union command

I also used the ORDER BY statement… So now I see, as much as I learn TSQL, more things I can do to manipulate the data.

The reference I used to learn Having and Union command is:

https://msdn.microsoft.com/en-us/library/ms187731.aspx

I recommend you to study there as well and try to understand as much as you can.

TIP: What helped me a lot to understand it, was to write each query and do it step by step like a back engineer, if you do it, I am sure you will learn so much more.

Leave a Comment

Your email address will not be published. Required fields are marked *