Post reply

Multi Level Count in TSQL

  • March 11, 2008 at 9:55 am

    #179971

    This seems simple enough, I'm trying to get a count of employees by different indicators. First total by job code, second by Union_Code.

    So I'm looking to count every one by distinct job codes inside a distinct Union_code, then total the number for that distinct union.

    Any help would be appreciated.

    SELECT emp.job_code,

    tbljob.job_title,

    emp.union_code

    FROM emp

    INNER JOIN

    empjob ON empjob.emp_id = emp.emp_id

    AND empjob.company = emp.company

    AND empjob.job_code_id = 'PRIM'

    INNER JOIN

    tbljob ON tbljob.job_code = empjob.job_code

    AND tbljob.company = empjob.company

    WHERE (emp.company = 'ABC')

    ORDER BY emp.union_code, empjob.job_code

  • March 11, 2008 at 11:03 am

    #788163

    i'm a bit confused by your post since you say you want a count for union and job, but your select has no count() and appears to list jobs and job titles.

    give this a try since it will produce the multi-level count you described. look for "ROLLUP operator" in Books Online for more info on ROLLUP.

    SELECT

    emp.union_code, emp.job_code, count(distinct emp.emp_id) as emp_ct

    FROM emp JOIN empjob

    ON empjob.emp_id = emp.emp_id

    AND empjob.company = emp.company

    WHERE empjob.job_code_id = 'PRIM'

    AND emp.company = 'ABC'

    GROUP BY emp.union_code, emp.job_code

    ORDER BY emp.union_code, emp.job_code

    WITH ROLLUP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply