SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 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.

Partitioning with the Over Clause (Part 2)

Yesterday we learned how the over clause can be used to compare your number against the overall aggregated number for an entire result set. Sometimes you might want your number to be compared against its category and not all records from a table. For example I don’t get any joy in saying I never won the state championship in Greco Roman wrestling. On the other hand with great joy I tell people was the Tacoma City Champion. By partitioning the sport down into City instead of the entire country or state my ranking is much better.

Businesses might have other reasons for partitioning a group. Let’s use an example of schools where they see most of them getting a drop in attendance during the summer time. Comparing an attendance trend from spring to summer would not show an uptrend even if your school is growing year after year. It makes more sense to compare this summer to last summer to find a trend then to compare the spring and summer where you always see a drop. Whatever your reason you can get a total by a level of your choosing with the OVER() clause. Now let’s try an example using the aggregate function COUNT( ) with the OVER( ) clause. We’ll start with a simple query on the Employee table. Here we see Alex Adams is just 1 employee out of 13.  In fact each employee is one of 13 in this example.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Advanced Aggregates with the Over Clause - Day 11 of 35 j2p_11_1

Now let’s look at Alex Adams. He works in Location 1. How many of JProCo’s employees are in Location 1 (Seattle)? And how many of the total employees work in Boston? Alongside the existing table records, we want to add a column showing the count of employees at each location. Recall that blank parentheses cause OVER( ) to apply the aggregation across all rows of a query. Our instruction to show employee count at each location means we don’t just want a total of all locations in our query – we already have that data displayed in the TotalEmployees column. We must include an argument inside the parentheses. PARTITION BY divides the result set into partitions. In this case we’ve added a partition by LocationID.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Advanced Aggregates with the Over Clause - Day 11 of 35 j2p_11_2

Similar to the behavior of GROUP BY, the command to PARTITION BY LocationID takes the LocationID for each employee and counts the number of records in that group which shares the same LocationID. Thus, each of the Seattle employees shows a 7 for LocationCount. The Boston employees have a LocationCount of 3, and the Spokane records have LocationCount of 2. John Marshbank shows LocationCount of 1, since he’s the only one with a null LocationID.

Since Seattle has 7 of the total 13 employees, we can guesstimate that just over ½ (or more than 50%) of JProCo employees work in Seattle (LocationID 1). Let’s run the query and check the results.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Advanced Aggregates with the Over Clause - Day 11 of 35 j2p_11_1

Our result set shows no ratios and all 0’s. This is because our calculation involved only integers. (The grant amount data was expressed in decimal form.) Rather than writing additional code using CAST, we’ll use a trick that works with many programming languages. Multiply the numerator by 1.0 to make each figure display as a decimal instead of an integer. Multiply the numerator by 1.0, so results appear as decimals instead of integers.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Advanced Aggregates with the Over Clause - Day 11 of 35 j2p_11_4

This result is a big improvement – each result is now in decimal form. The Seattle occupancy rate is about 53.85%.  Now multiply instead by 100 so the values show as percentages, like we did with the Grant example. In this instance be sure to multiply by 100.0, so the values show as decimals and not integers. 

Finally, polish your report by adding the descriptive title Pct in place of the blank column header. Sort your report in descending order of occupancy (Pct) and LastName.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Advanced Aggregates with the Over Clause - Day 11 of 35 j2p_11_5

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 11

Q 11) You have a table named CurrentProducts. The table contains a column named Category. You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category. Which query should you use?

  1. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  2. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  3. SELECT DISTINCT Category,
    COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
    FROM CurrentProducts

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)

Joes 2 Pros, SQL Scripts
Previous Post
SQL SERVER – Who needs ETL Version Control?
Next Post
SQL SERVER – SafePeak – The Plug and Play Immediate Acceleration Solution

Related Posts

108 Comments. Leave new

  • 1st option gives only number eg: 20
    3rd option gives in decimal but wrong answer eg:500.000000000000
    2nd option is correct…it gives 20.000000000000

    2.SELECT DISTINCT Category,
    COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
    FROM CurrentProducts

    Arjun

    India

    Reply
  • Answer : Option 2

    2) SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Chennai, TamilNadu, India

    Reply
  • Correct Answer is: B
    Because This Query only have proper syntax and multiple by 100.0 not 100. So we will get the Percentage with Decimal values.

    by GVPrabu || INDIA ||

    Reply
  • Hi,

    Q: You have a table named CurrentProducts. The table contains a column named Category. You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category. Which query should you use?

    2) SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Thanks pinal, I learn New Thing from Last To Day …..

    I am from India….

    Reply
  • P.Anish Shenoy
    August 11, 2011 11:21 am

    Hi Sir,

    Option no 2 is the correct answer as this will give the percentage of products in each product category with decimals

    2. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    option no 1 also give the result but with out decimals

    1. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    option no 3 will provide invalid result as the numerator and denominator are interchanged.

    3. SELECT DISTINCT Category,
    COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
    FROM CurrentProducts

    So the correct answer is option no 2.

    P.Anish Shenoy
    INDIA,Bangalore, Karnataka

    Reply
  • The elimination method:

    Q:: You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category.

    1… (with decimals) >> Eliminates 1st query.
    2… products in each product Category. >> Eliminates 3rd query.

    So, #2 has to be right and it is.

    Reply
  • The elimination method:

    Q:: You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category.

    1… (with decimals) >> Eliminates 1st query.
    2… products in each product Category. >> Eliminates 3rd query.

    So, #2 has to be right and it is.

    Kedar (India)

    Reply
  • Correct Answer is 2

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Rajesh Garg
    India

    Reply
  • Nakul Vachhrajani
    August 11, 2011 11:42 am

    Correct Answer: #2
    Country of residence: India

    Reply
  • Hi,

    Correct answer is 2

    We have to use “100.0”, so it will give us result with Decimals (Accurate)
    Option 1: will give us result in INT, so its not that much accurate

    COUNTRY: INDIA

    Reply
  • Answer 2 is correct:

    2.SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    City: Baroda
    Country: India

    Thanks,
    GurjitSingh

    Reply
  • Option 2 is correct answer as

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    because it displays categories with percentages in decimal values.

    Explaniation:

    1.First and second query are same there is only difference we are multiple with decimal value in second query that why! second query is returning in decimal. here //T-SQL statement that calculates the percentage (with decimals) of products in each product Category.// for each products we are getting decimal values
    In SQL Server, The result is decimal when either of the following is true:
    # Both expressions are decimal.
    # One expression is decimal and the other is a data type with a lower precedence than decimal.
    so here count is integer value and percentage [100.0] is contains decimal.

    2. third query returns count of all rows as over clause and calculating but as per your question here we need //T-SQL statement that calculates the percentage (with decimals) of products in each product Category.// so third query is wrong..

    Thanks,

    Vijayakumar P Kochi (India)

    Reply
  • 2nd option is correct:

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    City: Baroda
    Country: India

    Reply
  • The Correct Answer is : 2

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Mohd Thoufeek
    India
    Chennai

    Reply
  • The correct Option is 2.

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    India

    Reply
  • Krishna Prakash. H. N.
    August 11, 2011 12:16 pm

    Correct Answer is 2

    %age should be decimal point.

    Krishna Prakash, India

    Reply
  • Dayanand Singh
    August 11, 2011 12:33 pm

    Correct Answer is Option 2

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Explanation: Option 1 provides the percentage of products in each product Category but not with decimals as there is not a single expression in decimals so result will be in integers only. In option 2 count of each type of category is divided by total count of products ad as it is multiplied by a expression having decimal points, the result will be with decimals.
    and Option 3 will produce wrong result as it divides count of all products with count of each type of product.

    Country – INDIA (Gujarat)

    Reply
  • Answer 2 :

    SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Country : India

    Reply
  • Rupesh Kumar Mishra
    August 11, 2011 12:51 pm

    2.SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts

    Reply
  • Hello!

    Option 2 will get the desired result.

    Option 1 will suffer from calculating only with integers.
    Option 3 does not use the “partition by” clause for the first over clause. Thus leads to wrong results since it will calculate always with all categories.

    Best wishes,

    Michael Mikic
    from Germany

    Reply

Leave a Reply