GROUP BY using parameters in SQL

Question

I am trying to somehow group a report based on a drop-down list of parameters that is pre-defined. I want to be able to subtotal the Total Hours or Total Pay of my report based on Department or JobCode. I have created the parameters and have no problem with that, I just am not sure if it's possible to use those parameters to call out a grouping command. Below is the spirit of what I am wanting, but the GROUP BY clause doesn't work for me even without a parameter.

SELECT EmployeeID, LastName, FirstName, Department, JobCode, PayRate, SUM(Hours) as "Total Hours", SUM(Pay) as "Total Pay"
FROM Employees
GROUP BY @GroupBy

I am truly a novice when it comes to SQL, so any help is very much appreciated.

Thank You.

Answer 1

The requirement is not 100% clear to me, but I imagine your after something like this:

select
  case when @groupBy = 'dept' then
       department else
       jobCode    end  dept_jobCode,
  sum(hours)
from employees
group by 
  case when @groupBy = 'dept' then
       department else
       jobCode    end;

To be tried with this setup:

create table employees (
  lastName   varchar(20),
  department varchar(20),
  jobCode    varchar(20),
  hours      number
);

insert into employees values ('Miller', 'Dept 1', 'A', 10);
insert into employees values ('Doe'   , 'Dept 1', 'A',  7);
insert into employees values ('Baker' , 'Dept 1', 'B',  4);

insert into employees values ('Sand'  , 'Dept 2', 'B',  6);
insert into employees values ('Stark' , 'Dept 2', 'B',  9);
insert into employees values ('Gild'  , 'Dept 2', 'A',  9);

Obviously, you want to set @groupBy to either 'dept' or any other value.

Answer 2

I think you are fundamentally misunderstanding how GROUP BY works.

GROUPING is a way to aggregate many rows together.

If you return any fields not in the GROUP BY, you need to decide what to do with them. You can't NOT do anything with them because you could have multiple values per group. They must be either excluded or aggregated.

To aggregate them, you need to decide what function to use (MAX, MIN, SUM, AVG, etc).

If you want to show many rows, say one per employee, but want to include some information about totals for that employee's department, you need to use a subquery:

SELECT employeeid, <other unaggregated fields>
FROM MyTable t
INNER JOIN (SELECT DepartmentID, SUM(Totalhours) as TotHours...etc
            FROM SomeOtherTable
            GROUP BY DepartmentID) as Sub
  ON Sub.DepartmentID = t.departmentID

There may be a way to do this dynamically but that is a pretty poor idea.