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?
- SELECT *, Min(HireDate) as StartDate FROM Employee
- SELECT *, Max(HireDate) as StartDate FROM Employee
- SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
- SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
Please post your answer in comment section to win Joes 2 Pros books.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
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)
124 Comments. Leave new
The correct answer is No. 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Because all fields of the Employee table are shown, and an additional field called StartDate shows the first HireDate for all Employees.
OVER() gives the supporting aggregated language needed to support the MIN( ) aggregate function.
Rene Castro
El Salvador
Correct option in answer 3:
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Thanks.
Country – India
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?
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.
Leo Pius
USA
3. Select lowest value from HireDate to be displayed next to each row
Dan Q
United States
Correct option is 3.
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
New Delhi
Explanation:
To show all fields of the table we will use the following query:
SELECT * FROM Employee
Since we want to display 1 more field called StartDate which will have the value of the first HireDate for all Employees we need to add Min(HireDate) OVER() as StartDate in the query
Correct Query is :
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Country of Residence:
United States
Answer is 3.
Query 1 can’t be used because there is no GROUPBY clause and to use Aggregate methods Group by need to be used.
Other queries 2 or 4 used MAX and we need Min value, so its incorrect.
Thanks for the wonderful question.
Regards,
Sudhir
New Delhi, India
The correct answer is option 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
as we want to show first hiredate, which can be calculated using MIN() function
and Over() clause will print it for all the records
Sumit
India
Question 10
Ans : 3 – SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Chennai , INDIA
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?
Wrong:Gives an Error as Aggregate function is used
1.SELECT *, Min(HireDate) as StartDate FROM Employee
Wrong:Gives an Error as Aggregate function is used
2.SELECT *, Max(HireDate) as StartDate FROM Employee
Correct:As over clause is used.Executes Correctly.
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Wrong:Executes correctly but uses Max date which gives recently joined Employee’s date
4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
Thanks for the Post :-)
Country :India
The Answer is
1.SELECT *, Min(HireDate) as StartDate FROM Employee
Country :India[Ahmedabad]
This is wrong. You will get an error
Correct Answer is# 3
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Country: India
Correct Ans is option 3.
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Pratik Raval
India
Option 3 is correct
INDIA
Correct Answer is: Option 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
–> As Min(HireDate) shows the First Hire date for all employees as a new column ‘StartDate’.
Thanks,
Dips
INDIA [Noida]
Option 3 is the right answer.
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Neelesh
India
The Correct option is 3
i.e;
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
from,
Hyderabad,AndhraPradesh,India.
The correct answer is option 3 that is
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
As here we want first i.e. minimum hire date from all employee for for using MIN() function without using any group we want to add OVER() also.
Mahmad Khoja
INDIA
AHMEDABAD
The correct answer would be
3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Sandipak
Location: Minneapolis, USA
Option 3 is correct
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Min(Hiredate) will show the first hiredate exist in the employee table, OVER() will help to remove the aggregate function with group by error.
India