• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Generating Frequency and Percentage in SQL Server

I have the data in my SQL Server tables like below
CaseID  Variable  Value
1            Married   1
2            Married   0
3            Married   1
4            Married   0
5            Married   1

I want the freqs and percentage like

VariableName  Code  Freqs Percent
Married              1          3     60
Married              0          2     40

Is there something in SQL Server to efficiently create frequency and percentage for the data similar to SAS or other Statistical Software? My current option is to write SAS program to read the data and generate the freqs and percentage, Wanted to find out what if any options are available in SQL Server.



0
asp123
Asked:
asp123
3 Solutions
 
k_rasuriCommented:
select  variable, [value], count(*) as frequency,
((select  cast (count(*) as float) from status s2 where s2.value=s1.value
group by  variable, value)/(select cast (count(*) as float) from status
s3 where s1.variable=s3.variable group by variable)*100) as percentage from status s1
group by  variable, value
0
 
Scott PletcherSenior DBACommented:
For efficiency, it's best to use a temp table, otherwise you have to scan the main table too many times.  For example [code below uses NULL to get grand total; if you have nulls in the actual values, you will need to use some other value]:

/* -- use to create sample data
set nocount on
drop table #tablename
create table #tablename (
    caseid int,
    variable varchar(40),
    value int
    )
insert into #tablename values(1 ,         'Married'  ,1)
insert into #tablename values(2  ,        'Married'  ,0)
insert into #tablename values(3   ,       'Married'  ,1)
insert into #tablename values(4    ,      'Married' , 0)
insert into #tablename values(5     ,     'Married',  1)
*/


SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#work') IS NOT NULL
    DROP TABLE #work
CREATE TABLE #work (
    variable VARCHAR(40),
    value INT,
    valueCount INT,
    UNIQUE CLUSTERED (variable, value)
    )

INSERT INTO #work
SELECT variable,
    CASE WHEN whichTotal = 'value' THEN value ELSE NULL END AS [value],
    COUNT(*) AS [valueCount]
FROM #tableName
CROSS JOIN (
    SELECT 'value' AS whichTotal UNION ALL  --total for an individual value
    SELECT 'variable'  --grand total for a variable (all values for one variable)
) AS whichTotals
GROUP BY variable,
    CASE WHEN whichTotal = 'value' THEN value ELSE NULL END
ORDER BY variable, value

SET NOCOUNT OFF

SELECT variable AS VariableName,
    value AS Code,
    valueCount AS Freqs,
    CAST(valueCount * 100.0 / (SELECT valueCount FROM #work w2
        WHERE w2.variable = w1.variable AND w2.value IS NULL) AS INT)
    AS [Percent]
FROM #work w1
WHERE value IS NOT NULL
ORDER BY variable, value
0
 
LowfatspreadCommented:
Select x.*
      ,convert(decimal(3,0),((x.Freq/Y.TotFreq) * 100)) as Percent
  From (
select variable,value,convert(decimal(9,3),count(*)) as Freq
  from yourtable
 group by variable,value
) as x
Inner Join
 ( select variable,Count(distinct value) as NoVal,Count(*) as TotFreq
    from yourtable
   group by variable
 ) as Y
 on x.variable=y.variable
order by a.variable,a.value
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now