# 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.

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.

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.

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.

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.

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)

## SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

#### 108 Comments.Leave new

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

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

Chennai, TamilNadu, India

Reply
• Correct Answer is 2

India

Reply
• rama chandra sahu
August 11, 2011 11:13 pm

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

India

Reply
• Dnyanesh Deshpande
August 11, 2011 11:17 pm

Answer ::—>

1. SELECT DISTINCT Category, COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
FROM CurrentProducts
–This option will be incorrect because,statement will calculate percentage without decimal.

2.SELECT DISTINCT Category, COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
FROM CurrentProducts
–This is correct option.

3.SELECT DISTINCT Category, COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
FROM CurrentProducts
–In this case formula to calculate percentage is incorrect so, this option will generate wrong result. So this is incorrect option.

County- India

-Dnyanesh

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

Gordon Kane
Allen TX
USA

Reply
• Leonardo Guerrero
August 11, 2011 11:37 pm

The correct answer is option 2
SELECT DISTINCT Category,
COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
FROM CurrentProducts

because option 1 show result wih no decimals, and option 3 whow wrong information

Leonardo

From: Chile

Reply
• The correct Answer is Option #2.

Shyam
Country: USA

Reply
• Sayli Deshmukh (@sddesh)
August 12, 2011 12:34 am

Thanks for the example. Answer is 2..

Sddesh
USA

Reply
• dilipkumarjena
August 12, 2011 12:42 am

Hi Pinal Sir,

The answer for the above question is Option no 2 is the correct Option as this calculates the percentage (with decimals) 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 1) This gives us the results products in each product Category but with out decimals

Option 3)This option provide invalid result as the numerator and denominator are reversed and will not fetch the result.

So the option left and which gives us the result is is option no 2.

DILIP KUMAR JENA
Country : INDIA

Reply
• vinay (@vinayprasadv)
August 12, 2011 12:49 am

Answer is :

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

Vinay,
Pune
India

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

To calculate the percentage (with decimals) of products in each product Category.

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

Option 1 -> has results in integer without the decimals

Option 3 -> is wrong as it has total product count in the numerator

country : USA

Reply
• Jagdish Prajapati
August 12, 2011 9:30 am

Option 2 is correct
INDIA

Reply
• Nagaraj Ejanthkar
August 12, 2011 10:49 am

Hi Pinal,

I checked your Facebook link and I didn’t find the winner for this post mentioned explicitly. Its the same page as this one.

Am I missing something ?

Thanks

Reply
• Correct Answer is option 2.

INDIA

Reply
• Option 2 is correct one.
I am from India

Reply
• Hi

The correct answer is #2.

Love this series.

Eric
USA

Reply
• #2

Will provide with decimals by utilizing the multiplier of 100.0 versus 100

Dan
New Jersey USA

Reply
• Check SQL Server 2011 “Denali” Books Online – there are a lot of improvements to analytical/ranking functions. Also OVER clause was enhanced very much.

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

A.) Because multiplying an aggregate by 100 (an integer) will result in the nearest integer (1) will not work. Since alculating a category’s percentage of the total products is done by dividing the quantity in the category by the quantity of all the products then multiplying by 100.0 (3) will be incorrect because it is dividing the total (COUNT(*) OVER ( )) by the category (COUNT(*) OVER(PARTITION BY Category)) instead. Because the category’s quantity is being multiplied by 100.0 (a decimal) before being divided by the quantity of all the products it will result in a decimal making (2) the correct answer.

Winner from USA: Nagaraj Ejanthkar

Winner from India: P.Anish Shenoy

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

Reply