?
Solved

Calculate percentage SQL using over()

Posted on 2010-01-11
5
Medium Priority
?
941 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 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 57

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 57

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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