如何使用GROUP BY上的子查询/函数对SQL Server 2008 R2执行查询

When I execute this query, it works fine:

当我执行此查询时,它工作正常:

SELECT     
pr.pr_nombre , cl.cl_nomcorto, 
mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951 as Interes,
mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951*.049 as WH,
(select pr_id from dbo.nonplusultra(cr.pr_id) where pr_renew_ref is null) as Agrupador
FROM   movcuentas mc
inner join corridas cr on mc.cr_id =  cr.cr_id
inner join clientes cl on cr.cl_id = cl.cl_id
inner join prestamos pr on cr.pr_id = pr.pr_id
WHERE     (mc_concepto = 'Amort Int') AND (tm_id = 3) AND MONTH(mc_fecha) = 2 AND YEAR(mc_fecha) = 2017 
ORDER BY pr.pr_nombre

I get this:

我明白了:

Query results

Now I need to show it adding columns "Interes" and "WH" on records with same "Agrupador", something like this:

现在我需要显示它在具有相同“Agrupador”的记录上添加“Interes”和“WH”列,如下所示:

SELECT     
pr.pr_nombre , cl.cl_nomcorto, 
sum(mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951) as Interes,
sum(mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951*.049) as WH,
(select pr_id from dbo.nonplusultra(cr.pr_id) where pr_renew_ref is null) as Agrupador
FROM   movcuentas mc
inner join corridas cr on mc.cr_id =  cr.cr_id
inner join clientes cl on cr.cl_id = cl.cl_id
inner join prestamos pr on cr.pr_id = pr.pr_id
WHERE     (mc_concepto = 'Amort Int') AND (tm_id = 3) AND MONTH(mc_fecha) = 2 AND YEAR(mc_fecha) = 2017 
GROUP BY pr.pr_nombre , cl.cl_nomcorto, (select pr_id from dbo.nonplusultra(cr.pr_id) where pr_renew_ref is null)
ORDER BY pr.pr_nombre

And I get this error message:

我收到此错误消息:

Msg 144, Level 15, State 1, Line 12 Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

消息144,级别15,状态1,行12不能在用于GROUP BY子句列表的表达式中使用聚合或子查询。

Any help will be welcome.

欢迎任何帮助。

1 个解决方案

#1

Can you make use of CTE here?

你能在这里使用CTE吗?

;WITH CTE as 
(
SELECT     
pr.pr_nombre , cl.cl_nomcorto, 
mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951 as Interes,
mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951*.049 as WH,
(select pr_id from dbo.nonplusultra(cr.pr_id) where pr_renew_ref is null) as Agrupador
FROM   movcuentas mc
inner join corridas cr on mc.cr_id =  cr.cr_id
inner join clientes cl on cr.cl_id = cl.cl_id
inner join prestamos pr on cr.pr_id = pr.pr_id
WHERE     (mc_concepto = 'Amort Int') AND (tm_id = 3) AND MONTH(mc_fecha) = 2 AND YEAR(mc_fecha) = 2017 
ORDER BY pr.pr_nombre
)
select pr_nombre,cl_nomcorto,sum(Interes),sum(WH),Agrupador
from CTE
group by pr_nombre,cl_nomcorto,Agrupador
order by pr_nombre