Unduplicated counts over various dimensions

Question

I'm working with some data on college students, which has a unique key of SSN, Campus, Credential (type of degree sought, like "Bachelor's"), ProgCat (broad subject area, like "History"), Program (specific subject area, like "European History"), and SchoolYear. I want to calculate a lot of statistics (for the sake of this example, we'll just stick with basic counts of number of students) and I want those statistics to be calculated for various combinations of aggregation of Campus, Program, and Program Category. In other words, I want to calculate statistics that get as specific as "number of students getting a bachelor's degree in European History at Big Oak University," as general as "number of students getting a bachelor's degree in anything at any university" and every level in between.

Now, usually, I'd just use CUBE in my GROUP BY statement, which would give me exactly what I want. However, there's an added complication... I need unduplicated counts of students, and it's possible that one student is enrolled in two different subjects, or at two different universities. So, if Jim Bob is enrolled both at Big Oak University and Sunrise College, I'd want him to show up once in the statistics for Big Oak, once in the statistics for Sunrise, and once and only once in the statistics for all colleges grouped together. As far as I know, there's no way to accomplish this with CUBE.

I've therefore been pursuing some solutions to deal with this. What I've come up with is a dynamic SQL solution. Basically, I create a table to hold my output, then I repeatedly cycle through calling the same SQL code with different parameters and adding the output to the output table. The parameters control what level of aggregation I'm using. I use a simple subquery with row_number() to pull only one record per student at any level of aggregation, making sure I get unduplicated counts. It looks something like this...

IF OBJECT_ID('tempdb..##CountOut') IS NOT NULL
    DROP TABLE ##CountOut;
CREATE TABLE ##CountOut(Agency char(3), Credential varchar(2), Campus varchar(10), ProgCat varchar(2),
    Program varchar(6), SchoolYear smallint, Students int, Graduates int);

GO;

CREATE PROCEDURE ##CalcGroup
    @UseCampus bit,
    @UseProgCat bit,
    @UseProgram bit
AS
    declare @AllList varchar(max);
    declare @ShortList varchar(max);
    declare @SQL varchar(max);

    select @AllList='';
    select @ShortList='';

    if @UseCampus=1 begin
        select @AllList=@AllList+'Campus, ';
        select @ShortList=@ShortList+'Campus, ';
    end
    else
        select @AllList=@AllList+'''ALL'' as Campus, ';

    if @UseProgCat=1 begin
        select @AllList=@AllList+'ProgCat, ';
        select @ShortList=@ShortList+'ProgCat, ';
    end
    else
        select @AllList=@AllList+'''AL'' as ProgCat, ';

    if @UseProgram=1 begin
        select @AllList=@AllList+'Program, ';
        select @ShortList=@ShortList+'Program, ';
    end
    else
        select @AllList=@AllList+'''ALL'' as Program, ';

    select @SQL='
        insert into ##CountOut(Agency, Credential, Campus, ProgCat, Program, SchoolYear, Students, Graduates)
        select Agency, Credential, '+@AllList+'SchoolYear,
            count(*) as Students,
            count(ExitQuarter) as Graduates
        from
        (
            select Agency, Credential, '+@ShortList+'SchoolYear, ExitQuarter,
                row_number() over (partition by Agency, Credential, '+@ShortList+'SchoolYear, SSN order by ExitQuarter desc) as RN
            from CFS_ESC.dbo.webapp_Data
        ) as Inside
        where RN=1
        group by Agency, Credential, '+@ShortList+'SchoolYear;
    ';

    exec(@SQL);
GO

execute ##CalcGroup @UseCampus=1, @UseProgCat=1, @UseProgram=1;
execute ##CalcGroup @UseCampus=1, @UseProgCat=1, @UseProgram=0;
/* etc. */

Is there anything you see to improve the clarity or the performance of this code?

I'm also wondering if this is the best way to accomplish this task? The only other solution that I have thought of is to create multiple records for every student enrollment. In other words, each student would have a record with their campus, program category, and program specified, a second record with their campus and program category specified and their program set to "All", a third record with... etc. That would make the initial data I'm querying over about 6x larger, but I'd only have to run one query to get my results, and I wouldn't have to worry about dynamic SQL. Do you think that might be a better option?

Any other solutions out there?

I'd appreciate any and all feedback. I have absolutely no formal training in SQL, though I've taught myself plenty to accomplish most tasks thrown at me. I'm now just hoping to learn some best practices.

Answer 1

Don't worry my friend, you should still be able to use GROUP BY CUBE(), instead of doing a COUNT(student) of the students you should use a COUNT(DISTINCT student) which will make sure each student is counted only once for each row.

Here is an example that I threw together.

SELECT Student, Campus, Subject, COUNT(DISTINCT Student) 
FROM 
(
    SELECT 'Jim Bob' AS Student,'Big Oak' AS Campus,'History' AS Subject
    UNION ALL
    SELECT 'Jim Bob','Sunrise','Chemistry'
    UNION ALL
    SELECT 'Jim Bob','Sunrise','Art'
    UNION ALL
    SELECT 'Bob Jim','Big Oak','Chemistry'
)a
GROUP BY CUBE(Student, Campus, Subject)

Seems to do the job on my SQL-Server 2008, so it should work for you too.

EDIT: After a small back and forth discussion in the comments, I managed to throw this together.

;WITH Student_Wages AS 
(
    SELECT DISTINCT SSN, WAGE
    FROM Data
)

SELECT A.campus, A.subject, SUM(A.Students), AVG(A.Wage) 
FROM 
(
    SELECT 
    ISNULL(D.campus,'-') AS campus,
    ISNULL(D.subject,'-') AS subject,
    COUNT(DISTINCT D.SSN) AS Students,
    (SELECT AVG(S.wage) WHERE D.SSN IN (SELECT S.SSN)) AS Wage
    FROM #Data D
    INNER JOIN Student_Wages S ON D.SSN = S.SSN
    GROUP BY CUBE(D.campus, D.subject), D.SSN, S.ssn
) AS A
GROUP BY A.campus, A.subject
ORDER BY A.campus, A.subject

The CTE and the subquery that I used to get the average prevents the same student from affecting the average more than once.

Here is the SQL Fiddle.