0

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:

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.

Any help will be welcome.

Rolando M
  • 15
  • 4
  • I'm not sure it even makes sense to put that subquery in the `GROUP BY` list. If it is correlated, then you should wrap it in an aggregate function somehow in the `SELECT` clause. If it's not correlated, then just remove it from `GROUP BY` because it isn't doing anything. – Tim Biegeleisen Mar 09 '17 at 01:15
  • The idea is to use column "Agrupador" to Group rows with the same value, In the data result included, it would add "Interest" and "WH" for rows with a value of "731" – Rolando M Mar 09 '17 at 01:35

1 Answers1

0

Can you make use of CTE here?

;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
Rajesh Bhat
  • 791
  • 3
  • 8
  • 20
  • Hi Rajesh, it returns the exact same result as the original query, it launches but it doesn't group by the "Agrupador" column. Thank you!!. – Rolando M Mar 09 '17 at 14:30
  • You mean the result from CTE is not grouping by Agrupador? Can you please share the snapshot? There must be some difference in records-whitespace or something. – Rajesh Bhat Mar 10 '17 at 05:57
  • Hi Rajesh, in fact there was a whitesapece in a record that kept it from working correctly, otherwise the solution you suggested works like a charm. Thank you!! – Rolando M Mar 10 '17 at 15:13
  • @Rolando M, that's great!! Please mark it as answer. Thank you!! – Rajesh Bhat Mar 13 '17 at 04:16