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
5
Medium Priority
?
949 Views
Last Modified: 2012-05-08
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
Comment
Question by:xav056
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26286141
try this, looks better...

you acnnot do the same with ranking

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

and the sample to learn more about rank functions in sql...
select color, 100 * count(*) / t 
from colors, (select COUNT(*) as t from colors) data
group by color, t

Open in new window

0
 
LVL 27

Accepted Solution

by:
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
SET ANSI_PADDING ON
GO
CREATE TABLE CGL.T1(ID INT IDENTITY(1,1), Color varchar(10) NULL);
GO
SET ANSI_PADDING OFF

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;

Open in new window

SQL2005-OverClauseExample.png
0
 
LVL 61

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question