Get percetage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get percetage

Author

Topic

pyram
Starting Member

12 Posts

Posted - 2012-04-14 : 13:35:47

Hi, Im using Sql Server 2005.

--------------------------------------------------------------------------------

I have these tables:


CREATE TABLE [dbo].[Proyectos2](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Proyecto] [varchar](255) NULL
)
CREATE TABLE [dbo].[Clientes2](
	
	
	[Vendedora] [varchar](255) NULL,
	[Proyecto] [varchar](255) NULL
	
)

insert into proyectos2
values
(
'Proyecto1'
)
insert into proyectos2
values
(
'Proyecto2'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Mariel','Proyecto2'
)

query:


Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @sum = Stuff(
(
Select ',SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup
order by
	grouping(Vendedora),
	Vendedora;';

print @strSQL;
Execute (@strSQL);
GO

if you execute the code, you will get this:
Vendedora------Proyecto1---Proyecto2----Total
Jessica------------2-----------0----------2
Mariel-------------0-----------1----------1
Grand Total--------2-----------1----------3

I want to add another row with the percentage from the total for each column.
Something like this:

Vendedora------Proyecto1---Proyecto2----Total
Jessica------------2-----------0----------2
Mariel-------------0-----------1----------1
Grand Total--------2-----------1----------3
Percentage--------.66%--------.33%-------100%

how can i do it?

thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-04-15 : 02:10:37

Try something like this:


Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT * FROM CTE UNION ALL ' +
              'SELECT ''Percentage'', ' + REPLACE(@Names, '],[', '] / Total, [') + ' / Total, ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

pyram
Starting Member

12 Posts

Posted - 2012-04-15 : 22:49:25

quote:

Originally posted by malpashaa

Try something like this:


Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT * FROM CTE UNION ALL ' +
              'SELECT ''Percentage'', ' + REPLACE(@Names, '],[', '] / Total, [') + ' / Total, ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

thanks!

pyram
Starting Member

12 Posts

Posted - 2012-04-15 : 23:40:43

quote:

Originally posted by malpashaa

Try something like this:


Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT * FROM CTE UNION ALL ' +
              'SELECT ''Percentage'', ' + REPLACE(@Names, '],[', '] / Total, [') + ' / Total, ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

im getting this output:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------2.000000000000-----0.000000000000-------2
Mariel--------0.000000000000-----1.000000000000-------1
Grand Total---2.000000000000-----1.000000000000-------3
Percentage----0.666666666666-----0.333333333333-------100

I would like something like this:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------2
Mariel--------------0-------------------1--------------1
Grand Total---------2-------------------1--------------3
Percentage----------0.666666666666------0.333333333333---100

You know, just the percentage row with the decimals.
Is there a way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 12:16:42

why not do this formatting at your front end? whaich is your presntation layer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pyram
Starting Member

12 Posts

Posted - 2012-04-17 : 12:32:59

quote:
Originally posted by visakh16

why not do this formatting at your front end? whaich is your presntation layer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I already managed to do it in sql, look:


Declare @Names As Varchar(Max), @strSQL Varchar(Max), @PercentNames varchar(max) ;
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @PercentNames = Stuff((Select ',' + 'CAST(' + QuoteName(Proyecto)  + '* 100.0/Total AS DECIMAL(10,2))'
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) 
              SELECT *, CAST(Total * 100.0 / (select Total from cte where Vendedora = ''Grand Total'') as Decimal(10,2)) as [Percent]
              
              FROM CTE UNION ALL 
              SELECT ''Porcentaje'', ' + @PercentNames + ', ''100'', 100' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

But i still have a problem:
I want the numbers after the decimal point removed.
What can i do in this case?

pyram
Starting Member

12 Posts

Posted - 2012-04-17 : 13:32:49

i got it now. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:50:06

there's no problem in doing this in sql. But if its for a report then most of reporting tools have ability to calculate percentages prebuilt into it. In those days you might be better off returning only component fields from sql and doing % calculation in report especially when you've analytical engines available at reporting end.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources