# SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

## Aggregates with the Over Clause

You have likely heard the business term “Market Share”. If your company is the biggest and has sold 15 million units in an industry that has sold a total of 50 million units then your company’s market share is 30% (15/50 = .30). Market share represents your number divide by the sum of all other numbers. In JProCo the biggest grant (Ben@Moretechnology.com) is \$41,000 and the total of all grants is \$193,700. Therefore the Ben grant is 21.6% of the whole set of grants for the company.

The two simple queries in the figure below show all the Grant table records and the sum of the grant amounts.

If we want to show the total amount next to every record of the table – or just one record of the table – SQL Server gives us the same error. It does not find the supporting aggregated language needed to support the SUM( ) aggregate function.

Adding the OVER( ) clause allows us to see the total amount next to each grant. We see 193,700 next to each record in the result set.

The sum of all 10 grants is \$193,700. Recall the largest single grant (007) is \$41,000. Doing the quick math in our head, we recognize \$41,000 is around 1/5 of ~\$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head. We want the new column to divide each grant amount by \$193,700 (the total of all the grants).

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example:  the ratio 0.2116 represents a percentage of 21.16%. Multiplying a ratio by 100 will show the percentage. To finish, give the column a descriptive title, PercentOfTotal.

In today post we examined the basic over clause with an empty set of Parenthesis. The over clause actually have many variations which we will see in tomorrow’s post.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter5.0Setup.sql script from Volume 2.

## Question 10

You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

1. SELECT *, Min(HireDate) as StartDate FROM Employee
2. SELECT *, Max(HireDate) as StartDate FROM Employee
3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

### Rules:

Every day one winner will be announced from United States.
Every day one winner will be announced from India.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – Jump in Identity Column After Restart

• David Seefeld
August 11, 2011 2:44 am

The correct option is #3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Option 1 and 2 would generate errors. Aggregate functions used in this context would need a GROUP BY statement.

Option 4 grabs the Max(HireDate) which would be the last, not the first, hire date of all of the employees.

• A. Arul Prakash
August 11, 2011 2:44 am

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Minimum of Hiredate will be first hire date. And since there is no group by clause, 1) is invalid. 3) with over() clause will put the earliest hire date in each row.

Country : USA

August 11, 2011 3:27 am

Hola, interesante proyecto que sigo hace un par de años.

Aunque no corresponda mi participación, ya que, no vivo en India o EEUU, la respuesta correcta es la número 3.

Determina la menor fecha de contratación y la asocia a todas las ocurrencias que existen en la tabla..

(Option 3: SELECT *, Min(HireDate) OVER() as StartDate FROM Employee)

Vivo en Santiago de Chile,

• Thomas Riehle
August 11, 2011 3:29 am

If you do not use OVER() you will get an error in your query. Also, you want the FIRST hire date, so you do not want to use MAX(), which leaves only option #3.

Country: United States

• Kelly (@greeleygeek)
August 11, 2011 6:53 am

#3 SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Kelly
USA

• Correct answer is option 3

• Srikanth Nallamothu
August 11, 2011 11:48 am

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

• The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Cochin,INDIA

• Ritesh Choksi
August 11, 2011 4:26 pm

India

• Prasanna kumar.D
August 11, 2011 7:36 pm

Answer for today question – Option : 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Today blog is useful to know over clause in SQL

• Hello

Love this series. The correct answer is #3, SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks
Eric
USA

• Francisco Gil
August 12, 2011 3:22 am

The correct answer is the #3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Regards,
Francisco
Miami

• Q 10) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

A.) Because there is no supporting language for the aggregated field both (1) and (2) will not be correct. Because Max (HireDate) will return the greatest or most recent HireDate (4) is also incorrect. The correct code is (3) which uses the Min aggregate function and the OVER ( ) clause as supporting language.

Winner from USA: levpius

Winner from India: Deva Rajan

I thank you all for participating here. The permanent record of this update is posted on facebook page.

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country:India

• You’ve really captured all the esetsnilas in this subject area, haven’t you?

• select *,HIREDATE -max(hiredate) over() as diff from emp2

• Uday Bhoopalam
August 16, 2011 9:58 pm

Correct answer is #3. I am coming across over() for the first time

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Uday
USA

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks,
Wayne (USA(

• Suvendu Shekhar Giri
October 16, 2011 12:57 pm

It helped me a lot , Thanks