CODEWITHSUNDEEP
sqlsql-servert-sql
eugeneK
eugeneK

Reputation: 11116

TSQL help with statistics query

I've made a post here a link , asking some question about how to make statistics for number of users with from same age group so i can show pie chart. I want to take query in older post to higher level with percentages along with values...

Means i to have count(userID) of certain age group / count(userID) of all * 100 to get percentage. While i want to get results with 10% or more all others should be listed as others with their total percentage.

This initial query made by Martin Smith

With Ages As
(
select count(userID) ageCount,userBirthYear 
from Users
group by userBirthYear
)

SELECT ageCount,userBirthYear FROM Ages WHERE ageCount>5
UNION ALL
SELECT sum(ageCount) ,'others' As userBirthYear FROM Ages WHERE ageCount<=5

thanks

Upvotes: 1

Views: 225

Answers (3)

Brock Adams
Brock Adams

Reputation: 93483

A straightforward way but requires two queries. Tested and seems to work...

DECLARE @NumUsers   INTEGER
SELECT  @NumUsers   = COUNT (userId)    FROM    Users


SELECT
    SUM (CntByYear.ageCount)                AS ageCount,
    SUM (CntByYear.agePercent)              AS agePercent,
    CntByYear.userBirthYear
FROM
    (
        SELECT
            COUNT (u.userId)                        AS ageCount,
            COUNT (u.userId) * 100.0 / @NumUsers    AS agePercent,
            CASE
                WHEN
                    COUNT (u.userId) * 100.0 / @NumUsers >= 10
                THEN
                    CAST (u.userBirthYear AS varchar)
                ELSE
                    'Other'
            END AS userBirthYear
        FROM
            Users   u
        GROUP BY
            u.userBirthYear
    ) AS CntByYear
GROUP BY
    CntByYear.userBirthYear
ORDER BY
    ageCount DESC,
    CntByYear.userBirthYear

Upvotes: 2

Amy B
Amy B

Reputation: 110111

Enhancing my answer from the previous question:

DECLARE @UserCount int
SET @UserCount = (SELECT COUNT(*) FROM Users)

SELECT 
  SUM(sub.ageCount) as ageCount, 
  sub.userBirthYear,
  100 * SUM(sub.ageCount) / nullif(@UserCount,0) as Percent
FROM 
( 
  SELECT Count(userId) ageCount, 
    CASE WHEN COUNT(userID) * 10 >= @UserCount
         THEN userBirthYear 
         ELSE 'Other'
    END as userBirthYear 
  FROM Users 
  GROUP BY userBirthYear 
) as sub 
GROUP BY sub.userBirthYear
ORDER BY 1 desc

Upvotes: 0

Baaju
Baaju

Reputation: 2010

This would work

WITH Ages AS (
    select  COUNT(userID) as agecount, (count(userID)*100/(SELECT   SUM(agecount) FROM 
    (select count(userID) ageCount,userBirthYear  
    from    Users 
    group   by userBirthYear )B)) percentage, userBirthYear  
    from    Users 
    group   by userBirthYear )

    SELECT  userBirthYear, agecount, Percentage FROM Ages WHERE Percentage > 10
    UNION
    SELECT  'OTHERS',sum(agecount), sum(Percentage) FROM Ages WHERE Percentage <= 10

Upvotes: 1

Related Questions