percentage - 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

General SQL Server Forums

New to SQL Server Programming

percentage

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-17 : 17:20:42

will like to get the percentage for the testgroups and group by purchasesiteid

result:

10%
15%
25%
50%

10%
40%
50%

declare @t1 table (counts int, purchasesiteid int, testgroup varchar(50))

insert @t1
select 100, 1024, NULL union all
select 50, 1024, 'Control' union all
select 20, 1024, 'Production' union all
select 30, 1024, 'Beta' union all
select 40, 2048, 'Production' union all
select 10, 2048, 'Beta' union all
select 50, 2048, NULL

select 100*counts/(select sum(counts) from @t1) as percentage, purchasesiteid, testgroup, counts from @t1
group by purchasesiteid, counts, testgroup
order by purchasesiteid

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-17 : 17:39:35

SELECT testgroup,purchasesiteid,counts,
100.00/(SUM(counts) OVER (PARTITION BY purchasesiteid)/cast(counts as real))
FROM @t1

http://connectsql.blogspot.com/2011/03/sql-server-magical-over-clause.html

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-18 : 18:22:45

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 02:44:06

See why need to use 100.00
http://blog.beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-22 : 12:26:23

Is there a way to do it without using OVER PARTITION?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 12:36:17

quote:
Originally posted by basicconfiguration

Is there a way to do it without using OVER PARTITION?

whay whats the issue in using it?

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-22 : 14:26:54

No issues. I just want to know how to do it the othr way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-22 : 14:29:08

You mean you want a less elegant and possibly less efficient way to do this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-22 : 14:56:54

Here are some other less elegant ways :)

SELECT 
    T1.testgroup,
    T1.purchasesiteid,
    T1.counts,
    100.00/(T.SumCounts /cast(T1.counts as real))
FROM 
    @t1 AS T1
INNER JOIN
    (
        SELECT 
            purchasesiteid,
            SUM(counts) AS SumCounts
        FROM
            @t1
        GROUP BY
            purchasesiteid
    ) AS T   
    ON T1.purchasesiteid = T.purchasesiteid

SELECT 
    T1.testgroup,
    T1.purchasesiteid,
    T1.counts,
    100.00/(T.SumCounts /cast(T1.counts as real))
FROM 
    @t1 AS T1
OUTER APPLY
    (
        SELECT 
            purchasesiteid,
            SUM(counts) AS SumCounts
        FROM
            @t1
        WHERE
             purchasesiteid = T1.purchasesiteid           
        GROUP BY
            purchasesiteid
    ) AS T

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-25 : 18:15:56

quote:
Originally posted by tkizer

You mean you want a less elegant and possibly less efficient way to do this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

No, I just want to know how to do it. I'm trying to learn.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-25 : 18:16:06

thanks lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources