4
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Pimgd or anyone else... tried to edit this to add the SQL Fiddle link, but it still gives me the bizarre "error submitting edit" message. If anybody wants to add the link for me, that'd be great. \$\endgroup\$ – John Chrysostom Aug 18 '14 at 13:04
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Very true. This advice definitely works for counting students... Unfortunately, unless I'm mistaken, it breaks down when you need to count things like the graduation flag... and it definitely breaks down when you need to do things like AVG. Thanks for the review though! \$\endgroup\$ – John Chrysostom Aug 18 '14 at 11:12
  • \$\begingroup\$ Well, you should be able to use AVG(DISTINT xyz) to get your AVG and but I don't foresee a problem with the graduation flag as long as you add it to the GROUP BY CUBE() \$\endgroup\$ – PenutReaper Aug 18 '14 at 11:51
  • \$\begingroup\$ Don't think that will work, unfortunately. Records are identified as distinct by SSN. Say I want to get average salary for students. I can't use AVG(DISTINCT Salary), because two students might have the same salary. I need to do AVG(Salary) for distinct students... \$\endgroup\$ – John Chrysostom Aug 18 '14 at 12:25
  • \$\begingroup\$ Any chance you can throw together some dummy data for me to play with? It'd make it much easier for me, or anyone else that might decide to help, to have something to work with. I'm certain that this can be done without the need to use dynamic SQL though. \$\endgroup\$ – PenutReaper Aug 18 '14 at 12:47
  • \$\begingroup\$ Here's a SQL Fiddle with Dummy Data for what I'm hoping to accomplish. sqlfiddle.com/#!6/a05c5 Please note that SSN 930098285 is enrolled in both Math and History at BigOak University. Please also note that SSN 438268221 is enrolled in History at both Sunrise and BigOak. Finally, please note that this data is all completely fabricated and that the "SSNs" are random 9-digit numbers. \$\endgroup\$ – John Chrysostom Aug 18 '14 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.