GROUP BY using parameters in SQL

Question

I am trying to somehow group a report based on a drop-down list of parameters that are predefined. I want to be able to subtotal the Total Hours or Total Pay of my report based on Department or JobCode. I created the parameters and have no problems with this, I'm just not sure that you can use these parameters to call the grouping command. Below is the spirit of what I want, 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 really new when it comes to SQL, so any help is greatly appreciated.

Thanks.

Answer 1

The requirement is not 100% clear to me, but I think you are 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 check this setting:

 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 both 'dept' and any other value.

Answer 2

I think that you fundamentally misunderstand how GROUP BY works.

GROUPING is a way to combine multiple rows.

If you return any fields not to GROUP BY , you need to decide what to do with them. You cannot NOT do anything with them because you can have multiple values ​​for each group. They must be either excluded or combined.

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

If you want to show many lines, say one for one employee, but want to include some information about the results for this department of employees, 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 this is a pretty bad idea.

Answer 3

Group by simple.

You need to specify in Group by each field that is included in the select statement, and not served on an aggregate function.

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

Answer 4

Any column that you select that is not used by one of the aggregate functions (SUM, MIN, etc.) must be specified in the GROUP BY .

For instance,

  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

Answer 5

Thanks a lot to Rene Niffenegger. I used your suggestion ...

  case when @groupBy = 'dept' then department else jobCode end; 

and it worked like a charm for me. I used it in a procedure with two "In" variables. One for the Data Year I wanted, and the second for changing the final Group By team.