percent of total in a GROUP BY

percent of total in a GROUP BY

Post by John » Thu, 18 Mar 1999 04:00:00



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

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

- John.

 
 
 

percent of total in a GROUP BY

Post by John » Thu, 18 Mar 1999 04:00:00


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

Post by Richard Romle » Thu, 18 Mar 1999 04:00:00


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

Post by Glen » Fri, 19 Mar 1999 04:00:00


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:

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

>- John.

 
 
 

1. Calculated member - Percent of total

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

2. ODBC TEXT Driver - What's the score?

3. OLAP Calculated member for percent of total revenue.

4. Selecting records into a recordset

5. Percent of Total Query Help

6. Possible Bug?

7. Percent of Total in Crystal 4.5 Cross Tab Report

8. Reorganisation of Informix Servers

9. SQL: Percent of total count

10. SQL question: figuring percents on totals

11. MDX:Acumulated percent or total

12. Percent of total

13. Percent column working at break key totals - How