Problem with SQL query and Group By

Question

Problem with SQL query and Group By

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the following query that I do in SQL Server

select distinct f200_razon_social, f200_nit, f253_id AS Numero_auxiliar, 
f253_descripcion AS Auxiliar,
SUM(f353_total_db - f353_total_cr) as TOTAL
from t350_co_docto_contable
INNER JOIN t200_mm_terceros ON 
t200_mm_terceros.f200_rowid=t350_co_docto_contable.f350_rowid_tercero
INNER JOIN t353_co_saldo_abierto ON 
t353_co_saldo_abierto.f353_rowid_docto=t350_co_docto_contable.f350_rowid
INNER JOIN t253_co_auxiliares ON 
t253_co_auxiliares.f253_rowid=t353_co_saldo_abierto.f353_rowid_auxiliar
INNER JOIN t354_co_mov_saldo_abierto ON 
t354_co_mov_saldo_abierto.f354_rowid_docto=
t350_co_docto_contable.f350_rowid
WHERE f350_id_tipo_docto LIKE 'FV%' AND f200_nit = '1035877400' AND 
f350_id_periodo >= '201807'AND f353_total_db - f353_total_cr <> 0 
AND f253_id = '13050502'
GROUP BY f200_razon_social, f200_nit, f253_id, f253_descripcion

It brings me the following results:

So far my query is going well, now I explain something: The TOTAL field that is displayed is the sum of a field named f353_total_db minus another field named f353_total_cr as seen in the SQL script.

These values have a due date that can be seen in a field called f353_fecha_vcto , if I remain the fecha de vencimiento minus the fecha actual will give me a value that I call días vencidos , if the días vencidos are equal to or less than 0 I will add the values that have días vencidos equal to or less than zero in a field called Corriente , otherwise I'll add them to a field called Vencidos

And I did it by modifying my SQL script and it stayed like this:

select distinct f200_razon_social, f200_nit, f253_id AS Numero_auxiliar, 
f253_descripcion AS Auxiliar,
SUM(f353_total_db - f353_total_cr) as TOTAL,
case when DATEDIFF(day, f353_fecha_vcto, GETDATE()) > 0 then sum(distinct 
f353_total_db - f353_total_cr) else 0 end 'Vencidos',
case when DATEDIFF(day, f353_fecha_vcto, GETDATE()) <= 0 then 
sum(distinct f353_total_db - f353_total_cr) else 0 end 'Corriente'
from t350_co_docto_contable
INNER JOIN t200_mm_terceros ON 
t200_mm_terceros.f200_rowid=t350_co_docto_contable.f350_rowid_tercero
INNER JOIN t353_co_saldo_abierto ON 
t353_co_saldo_abierto.f353_rowid_docto=t350_co_docto_contable.f350_rowid
INNER JOIN t253_co_auxiliares ON 
t253_co_auxiliares.f253_rowid=t353_co_saldo_abierto.f353_rowid_auxiliar
INNER JOIN t354_co_mov_saldo_abierto ON 
t354_co_mov_saldo_abierto.f354_rowid_docto=
t350_co_docto_contable.f350_rowid
WHERE f350_id_tipo_docto LIKE 'FV%' AND f200_nit = '1035877400' AND 
f350_id_periodo >= '201807'AND f353_total_db - f353_total_cr <> 0 
AND f253_id = '13050502'
GROUP BY f200_razon_social, f200_nit, f253_id, f253_descripcion

Add two CASE This script throws me the following error

Mens. 8120, Level 16, State 1, Line 3

The column 't353_co_saldo_abierto.f353_fecha_vcto' in the selection list is not valid, because it is not contained in an aggregate function or in the GROUP BY clause.

When I add the field f353_fecha_vcto to GROUP BY it sends me the following results:

What is the problem? If they were fixed in my initial script when I only added a TOTAL I just launched a record, but now when I attach the field f353_fecha_vcto to GROUP BY brings me several records because each value has different Fecha de vencimiento .

I need to exclude f353_fecha_vcto from GROUP BY so that it only brings me one record and not several without modifying my CASE .

sql sql-server

asked by The_pacha 06.09.2018 в 19:09

source

2 answers

0

With the information that you give what I can say is that you make a subquery and there put your CASE, in this way you exclude the declaration in GROUP BY of f353_fecha_vcto , you only return the result that you of that subquery.

This is an example of subquery in T-SQL:

SELECT (SELECT * FROM Estados WHERE idEstado = Municipios.idEstado) AS Estado FROM Municipios

Here what happens is that you compare the id of the state of the table Municipalities in the subquery, that is why I put the table first and then its column. idEstado = Municipios.idEstado

answered by 06.09.2018 в 20:25

Split in SQL of a select How to evaluate conditionals in JAVA in this exercise?

score 1 · Accepted Answer

The error gives you a clue, the f353_fecha_vcto column should be defined in GROUP BY or within any of the aggregation functions, for example within SUM() . I understand that in your case, it should be within SUM is to say something like that

SUM(case when DATEDIFF(day, f353_fecha_vcto, GETDATE()) > 0 then distinct f353_total_db - f353_total_cr else 0 end) 'Vencidos',

Check the logic of the other CASE should be similar to what was commented, also the use of DISTINCT within SUM , I do not understand the meaning.