## percent of total in a GROUP BY

### percent of total in a GROUP BY

I came up with this but don't think it's very efficient:

FROM UserList
GROUP BY state WITH rollup

- John.

### percent of total in a GROUP BY

I would like to group by a field and have the number returned be a
percentage of the total.

eg. GROUP BY STATE

State    % of total
----------------------
CA    15% (.15)
NY    50% (.20)
TX    15% (.15)
WA    20% (.20)

Does anyone know of the best way to acomplish this, either with a Stored
Procedure or preferably a view (so I can just select from it with ADO)

I'm using SQL Server 7

thanks, John.

### percent of total in a GROUP BY

This example (from Pubs database) shows how to do what you want:

select a.state, 100*sum(case when a.state = b.state then 1.0 end)/count(*)
as pct
from authors a, authors b
group by a.state

which returns:

state pct
----- -----------
CA    65.217391
IN     4.347826
KS     4.347826
MD     4.347826
MI     4.347826
OR     4.347826
TN     4.347826
UT     8.695652

Because of the cross join, count(*) always contains the total rows in the
authors table. The case statement counts for each state in table "a", how
many rows are in the same state from table "b". The rest is easy.

Richard

>I would like to group by a field and have the number returned be a
>percentage of the total.

>eg. GROUP BY STATE

>State    % of total
>----------------------
>CA    15% (.15)
>NY    50% (.20)
>TX    15% (.15)
>WA    20% (.20)

>Does anyone know of the best way to acomplish this, either with a Stored
>Procedure or preferably a view (so I can just select from it with ADO)

>I'm using SQL Server 7

>thanks, John.

### percent of total in a GROUP BY

John,

Assuming there is an index on the UserList table, the following will
be faster.  I would also suggest a where clause.

Select
State,
(Cast(Count(*) as float) / Cast(NumRows as Float)) * 100  as
Avg_Total
from
UserList,
(Select
rows
from
SysIndexes
where
id=object_ID("UserList")
and
IndId < 2) as Total  (NumRows)
group by
State,
NumRows

>I came up with this but don't think it's very efficient:

>    CONVERT(float, (SELECT COUNT(username) FROM userlist))
>FROM UserList
>GROUP BY state WITH rollup

>- John.

Hi,
I have a measure: [NoOfCases], and a dimention: [Departments]
I want to build a calculate member: [PercentOfTotal], that will be the value
of [NoOfCases] for a specific department divided by that value for all
department.
Can yo please provide me the MDX syntax?
Thank you,
Nurit