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.

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

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.

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

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.

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

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.

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

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.

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

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

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)

,
Previous Post
SQL SERVER – Use INSERT INTO … SELECT instead of Cursor
Next Post
SQL SERVER – Who needs ETL Version Control?

Related Posts

124 Comments. Leave new

  • Answer 3

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

    Kulwant kumar
    Delhi India

    Reply
  • DiveSh Singhvi
    August 10, 2011 7:16 pm

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

    Divesh
    INDIA

    Reply
  • Jagdish Prajapati
    August 10, 2011 7:41 pm

    Hi all Option 3 is correct

    INDIA

    Reply
  • Shekhar Gurav.
    August 10, 2011 7:43 pm

    Correct Answer –

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

    Shekhar Gurav.
    Country : INDIA

    Reply
  • option 3 is the correct one.
    as first 2 queries are not valid.
    the min(hiredate) over() gives the first hire date of all the dates for that employee.
    which is the answer u expect .

    Boston USA

    Reply
  • Correct Answer is Option 3:

    OVER() gives us ability to use aggregate columns along with other table columns.
    Min() will give us the minimum value of the column.

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

    Country: INDIA

    Reply
  • Basavaraj Biradar
    August 10, 2011 8:13 pm

    Correct Answer is 3.

    Query in option 1 and 2 will give error. And Query in option 4 gives the last employees hiredate. So the Correct answer is 3.

    Thanks,
    Basavaraj Biradar
    India

    Reply
  • Dnyanesh Deshpande
    August 10, 2011 8:26 pm

    1.SELECT *, Min(HireDate) as StartDate FROM Employee
    Will generate error as we are not using Over().

    2.SELECT *, Max(HireDate) as StartDate FROM Employee
    Will generate error as we are not using Over().

    3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
    This statement will generate correct result.

    4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
    This Statement will not generate desired result because of Max().

    Thanks
    -Dnyanesh

    Reply
  • Dnyanesh Deshpande
    August 10, 2011 8:27 pm

    1.SELECT *, Min(HireDate) as StartDate FROM Employee
    Will generate error as we are not using Over().

    2.SELECT *, Max(HireDate) as StartDate FROM Employee
    Will generate error as we are not using Over().

    3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
    This statement will generate correct result.

    4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
    This Statement will not generate desired result because of Max().

    India

    Thanks
    -Dnyanesh

    Reply
  • Hi Pinal,

    Correct Answer is – # 3

    #4 doesn’t come in consideration as its looking for Max(HireDate) while we are looking for First hire date.

    #1 and #2 will give an error..

    Where #3 will give us as the requirement

    SELECT *, Min(HireDate) OVER() as StartDate FROM Employee is Correct Answer.

    I’m from Chicago, USA

    Reply
  • rama chandra sahu
    August 10, 2011 9:16 pm

    Correct Answer is# 3
    3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

    Country: India

    Reply
  • Option 3 is Correct.

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

    Option 1 and 2 will give error as Aggregate function is used without “group by”.

    Max function in statement 4 will not give the correct result.

    Yeou Sunn
    India

    Reply
  • answer is 3:
    SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

    Rahul Thapa

    India

    Reply
  • vinay (@vinayprasadv)
    August 10, 2011 10:34 pm

    Answer is:

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

    Vinay
    Pune, INDIA

    Reply
  • kaushik thanki
    August 10, 2011 10:34 pm

    The correct answer is 3

    kaushik thanki

    india.

    Reply
  • Kalyanasundaram.K
    August 10, 2011 11:03 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

    Chennai, Tamilnadu, India

    Reply
  • The best way to show all fields of the Employee table with an additional field called StartDate that shows the first HireDate for all Employees would be to use option 3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee.

    Country: United States

    Reply
  • Alagu Mano Sabari M
    August 10, 2011 11:07 pm

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

    Chennai, India

    Reply
  • Correct option in answer 3:

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

    Country – India

    Reply
  • dilipkumarjena
    August 11, 2011 12:12 am

    Hi Pinal Sir,

    The correct answer for above question is Option No. 3
    SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

    Explanation:

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

    In above query all the fields (columns) from the Employee table are selected, and a added field called StartDate shows the first HireDate for all Employees it is simple as we all know but then from your article you explained OVER() gives the supporting aggregated language needed to support the MIN( ) aggregate function and hence our problem is solved by this option.

    WHY Other options are wrong.

    OPTION 1) This option SELECT *, Min(HireDate) as StartDate FROM Employee error occurs as Aggregate function is used without “group by”.

    OPTION 2) This SELECT *, Max(HireDate) as StartDate FROM Employee query generates error as Aggregate function is used without “group by”.

    OPTION 4)SELECT *, Max(HireDate) OVER() as StartDate FROM Employee here it is giving max but we want minimum so wrong option.

    DILIP KUMAR JENA
    Country : INDIA

    Reply

Leave a Reply