Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Calculate percentage SQL using over()

Posted on 2010-01-11
Medium Priority
949 Views
I have a table with different products where each product has a color
How do I calculate the percentage of each product in the table by color
for simplicity assume that I only have a table with the following inforimation
Row 1 "RED"
Row 2 "RED"
Row 3 "RED"
Row 4 "RED"
Row 5 "Blue"

How to I get Red=80% and Blue 20% by using the Over() statement
I know I could do this by doing
Select COLOR, (Count(*))*100/(Select Count(*) FROM Colors)as perc
FROM COLORS
GROUP BY COLOR

But I would like to know How can I do this by something similar to
Select  Color ,(Count(*)*100/count(*) over())
FROM
Colors
GROUP BY Color
Partitonioning in the over does not help

Thanks
0
Question by:xav056
• 2

LVL 143

Expert Comment

ID: 26285766
you cannot. you have to do this way:
``````;with data as ( select color, count(*) cc from Colors group by color )
, total as ( select sum(cc) from data )
select d.*, d.cc/total.cc
from data d
cross join total
``````
0

LVL 61

Expert Comment

ID: 26286141
try this, looks better...

you acnnot do the same with ranking

check here: http://msdn.microsoft.com/en-us/library/ms189798.aspx

``````select color, 100 * count(*) / t
from colors, (select COUNT(*) as t from colors) data
group by color, t
``````
0

LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 26288624
To do it using OVER() you want something like this, prduced the following output.
``````USE [CGL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
GO
CREATE TABLE CGL.T1(ID INT IDENTITY(1,1), Color varchar(10) NULL);
GO

Insert Into T1 Values ('Red');
Insert Into T1 Values ('Red');
Insert Into T1 Values ('Red');
Insert Into T1 Values ('Red');
Insert Into T1 Values ('Blue');

SELECT *, 100.00 * COUNT(Color) OVER (PARTITION BY Color) / COUNT(*) OVER () Pct
FROM CGL.t1 T

DROP TABLE CGL.T1;
``````
SQL2005-OverClauseExample.png
0

LVL 61

Expert Comment

ID: 26289843
accepted solution is not what is asked for!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
###### Suggested Courses
Course of the Month13 days, 5 hours left to enroll