GROUP BY using parameters in SQL

问题:

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.

回答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.

回答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.

回答3:

Group by is simple really.

You have to list in group by every field that is included in the select statement and not fed to an aggregate function.

Which is why you can't have a variable group by with a fixed list of columns in select. (Well, you can in mysql, but it effectively applies virtual any() aggregate to them.)

回答4:

Any column you are selecting that are not used by one of the aggregate function (SUM, MIN, etc) needs to be listed in the GROUP BY clause.

For example,

SELECT EmployeeID, LastName, FirstName, SUM(Hours) as "Total Hours"
FROM Employees
GROUP BY EmployeeID, LastName, FirstName

Good examples here: http://www.w3schools.com/sql/sql_groupby.asp