GROUP BY使用SQL中的参数 [英] GROUP BY using parameters in SQL
问题描述
我试图以某种方式根据预先定义的参数下拉列表对报告进行分组。我希望能够根据Department或JobCode小计我的报告的总时数或总薪酬。我已经创建了参数并且没有任何问题,我只是不确定是否可以使用这些参数来调出分组命令。下面是我想要的精神,但即使没有参数,GROUP BY子句也不适用于我。
SELECT EmployeeID,LastName,FirstName,Department,JobCode,PayRate,SUM(小时)作为Total Hours,SUM(Pay)作为Total Pay
FROM Employees
GROUP BY @GroupBy
当谈到SQL时,我确实是一个新手,因此非常感谢任何帮助。
谢谢。
你的后面是这样的:
选择
时,@groupBy ='dept'然后
部门其他
jobCode end dept_jobCode,
总额(小时)
来自员工
组
当@groupBy ='部门'然后
部门其他$ b $时b $ b jobCode结束;
试试这个设置:
<$ (20),
department varchar(20),
jobCode varchar(20),
hours number($)pre> create table employees(
lastName varchar
);
插入员工值('Miller','部门1','A',10);
插入员工值('Doe','部门1','A',7);
插入员工值('贝克','部门1','B',4);
插入员工值('沙','部门2','B',6);
插入员工值('Stark','部门2','B',9);
插入员工值('Gild','部门2','A',9);
显然,您要设置 @groupBy
到'dept'
或任何其他值。
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.
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.
这篇关于GROUP BY使用SQL中的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!