Tema: Problemas con cláusula Group By
Ver Mensaje Individual
  #7 (permalink)  
Antiguo 12/01/2012, 17:51
Avatar de cesar203
cesar203
 
Fecha de Ingreso: enero-2010
Ubicación: LIMA
Mensajes: 27
Antigüedad: 15 años
Puntos: 0
Respuesta: Problemas con cláusula Group By
Doc Un favor grande tengo estas dos tablas

Código SQL: 
Ver original
  1. CREATE TABLE [dbo].[simdistritos](
  2.     [pais_cod] [CHAR](4) NOT NULL,
  3.     [prov_cod] [CHAR](4) NOT NULL,
  4.     [dist_cod] [CHAR](4) NOT NULL,
  5.     [dist_nom] [CHAR](50) NOT NULL,
  6.     [sicpc] [CHAR](20) NOT NULL,
  7.     [sicfecha] [datetime] NOT NULL
  8.  
  9. CREATE TABLE [dbo].[vemsuscripcion](
  10.     [emp_cod] [CHAR](10) NOT NULL,
  11.     [sus_id] [INT] NOT NULL,
  12.     [sus_anio] [INT] NOT NULL,
  13.     [sus_mes] [INT] NOT NULL,
  14.     [suc_cod] [CHAR](10) NOT NULL,
  15.     [sus_camp] [INT] NOT NULL,
  16.     [sus_fch] [smalldatetime] NOT NULL,
  17.     [sus_flgnueren] [INT] NOT NULL,
  18.     [sus_ficha] [CHAR](10) NOT NULL,
  19.     [sus_codigo] [CHAR](19) NOT NULL,
  20.     [coa_id] [INT] NOT NULL,
  21.     [coa_tdoc] [CHAR](2) NOT NULL,
  22.     [coa_ndoc] [CHAR](11) NOT NULL,
  23.     [coa_razsoc] [CHAR](80) NOT NULL,
  24.     [pais_cod] [CHAR](4) NOT NULL,
  25.     [prov_cod] [CHAR](4) NOT NULL,
  26.     [dist_cod] [CHAR](4) NOT NULL,
  27.     [coa_direc] [CHAR](70) NOT NULL,
  28.     [coa_email] [CHAR](200) NOT NULL,
  29.     [sus_aten] [CHAR](80) NOT NULL,
  30.     [coa_obs_cli] [text] NOT NULL,
  31.     [pais_cod_ent] [CHAR](4) NOT NULL,
  32.     [prov_cod_ent] [CHAR](4) NOT NULL,
  33.     [dist_cod_ent] [CHAR](4) NOT NULL,
  34.     [coa_direc_ent] [CHAR](70) NOT NULL,
  35.     [coa_direcref_ent] [CHAR](70) NOT NULL,
  36.     [coa_telfijo_ent] [CHAR](50) NOT NULL,
  37.     [coa_telcelu_ent] [CHAR](50) NOT NULL,
  38.     [sus_contac_ent] [CHAR](80) NOT NULL,
  39.     [coa_obs_clilog] [text] NOT NULL,
  40.     [pais_cod_cob] [CHAR](4) NOT NULL,
  41.     [prov_cod_cob] [CHAR](4) NOT NULL,
  42.     [dist_cod_cob] [CHAR](4) NOT NULL,
  43.     [coa_direc_cob] [CHAR](70) NOT NULL,
  44.     [coa_direcref_cob] [CHAR](70) NOT NULL,
  45.     [coa_telfijo_cob] [CHAR](50) NOT NULL,
  46.     [coa_telcelu_cob] [CHAR](50) NOT NULL,
  47.     [sus_contac_cob] [CHAR](80) NOT NULL,
  48.     [coa_obs_clicob] [text] NOT NULL,
  49.     [coa_id_vend] [INT] NOT NULL,
  50.     [coa_tdoc_vend] [CHAR](2) NOT NULL,
  51.     [coa_ndoc_vend] [CHAR](11) NOT NULL,
  52.     [coa_razsoc_vend] [CHAR](80) NOT NULL,
  53.     [art_cod] [CHAR](20) NOT NULL,
  54.     [art_nom] [CHAR](90) NOT NULL,
  55.     [sus_cant] [INT] NOT NULL,
  56.     [sus_inirep] [smalldatetime] NOT NULL,
  57.     [sus_finrep] [smalldatetime] NOT NULL,
  58.     [sus_inibol] [smalldatetime] NOT NULL,
  59.     [sus_finbol] [smalldatetime] NOT NULL,
  60.     [sus_iniase] [smalldatetime] NOT NULL,
  61.     [sus_finase] [smalldatetime] NOT NULL,
  62.     [sus_iniweb] [smalldatetime] NOT NULL,
  63.     [sus_finweb] [smalldatetime] NOT NULL,
  64.     [sus_flgconcre] [INT] NOT NULL,
  65.     [sus_mon] [INT] NOT NULL,
  66.     [sus_tc] [NUMERIC](9, 3) NOT NULL,
  67.     [imp_pl_mn] [NUMERIC](9, 2) NOT NULL,
  68.     [imp_pv_mn] [NUMERIC](9, 2) NOT NULL,
  69.     [imp_ini_mn] [NUMERIC](9, 2) NOT NULL,
  70.     [imp_sld_mn] [NUMERIC](9, 2) NOT NULL,
  71.     [imp_pl_me] [NUMERIC](9, 2) NOT NULL,
  72.     [imp_pv_me] [NUMERIC](9, 2) NOT NULL,
  73.     [imp_ini_me] [NUMERIC](9, 2) NOT NULL,
  74.     [imp_sld_me] [NUMERIC](9, 2) NOT NULL,
  75.     [sus_flgttg] [INT] NOT NULL,
  76.     [sus_cuotas] [INT] NOT NULL,
  77.     [sus_flglet] [INT] NOT NULL,
  78.     [sus_cuo_01] [CHAR](10) NOT NULL,
  79.     [sus_fch_01] [smalldatetime] NOT NULL,
  80.     [sus_imp_01] [NUMERIC](9, 2) NOT NULL,
  81.     [sus_cuo_02] [CHAR](10) NOT NULL,
  82.     [sus_fch_02] [smalldatetime] NOT NULL,
  83.     [sus_imp_02] [NUMERIC](9, 2) NOT NULL,
  84.     [sus_cuo_03] [CHAR](10) NOT NULL,
  85.     [sus_fch_03] [smalldatetime] NOT NULL,
  86.     [sus_imp_03] [NUMERIC](9, 2) NOT NULL,
  87.     [sus_cuo_04] [CHAR](10) NOT NULL,
  88.     [sus_fch_04] [smalldatetime] NOT NULL,
  89.     [sus_imp_04] [NUMERIC](9, 2) NOT NULL,
  90.     [sus_cuo_05] [CHAR](10) NOT NULL,
  91.     [sus_fch_05] [smalldatetime] NOT NULL,
  92.     [sus_imp_05] [NUMERIC](9, 2) NOT NULL,
  93.     [sus_cuo_06] [CHAR](10) NOT NULL,
  94.     [sus_fch_06] [smalldatetime] NOT NULL,
  95.     [sus_imp_06] [NUMERIC](9, 2) NOT NULL,
  96.     [sus_cuo_07] [CHAR](10) NOT NULL,
  97.     [sus_fch_07] [smalldatetime] NOT NULL,
  98.     [sus_imp_07] [NUMERIC](9, 2) NOT NULL,
  99.     [sus_cuo_08] [CHAR](10) NOT NULL,
  100.     [sus_fch_08] [smalldatetime] NOT NULL,
  101.     [sus_imp_08] [NUMERIC](9, 2) NOT NULL,
  102.     [sus_cuo_09] [CHAR](10) NOT NULL,
  103.     [sus_fch_09] [smalldatetime] NOT NULL,
  104.     [sus_imp_09] [NUMERIC](9, 2) NOT NULL,
  105.     [sus_cuo_10] [CHAR](10) NOT NULL,
  106.     [sus_fch_10] [smalldatetime] NOT NULL,
  107.     [sus_imp_10] [NUMERIC](9, 2) NOT NULL,
  108.     [sus_flgretrep] [INT] NOT NULL,
  109.     [sus_flgretbol] [INT] NOT NULL,
  110.     [sus_flgretase] [INT] NOT NULL,
  111.     [sus_flgretweb] [INT] NOT NULL,
  112.     [sus_flggendocvta] [INT] NOT NULL,
  113.     [doc_tipo] [CHAR](2) NOT NULL,
  114.     [doc_serie] [CHAR](4) NOT NULL,
  115.     [doc_nro] [INT] NOT NULL,
  116.     [doc_est] [CHAR](1) NOT NULL,
  117.     [mov_id] [INT] NOT NULL,
  118.     [vou_serie] [CHAR](4) NOT NULL,
  119.     [vou_nro] [INT] NOT NULL,
  120.     [sus_flgrep] [INT] NOT NULL,
  121.     [sixpc] [CHAR](20) NOT NULL,
  122.     [sixfecha] [datetime] NOT NULL,
  123.     [sixusuario] [CHAR](10) NOT NULL,
  124.     [sus_flgorigen] [CHAR](4) NOT NULL,
  125.     [sus_flgorigen_id] [NUMERIC](10, 0) NOT NULL,
  126.     [sus_est] [CHAR](1) NOT NULL,
  127.     [sicpc] [CHAR](20) NOT NULL,
  128.     [sicfecha] [datetime] NOT NULL,
  129.     [sicusuario] [CHAR](10) NOT NULL,
  130.     [simpc] [CHAR](20) NULL,
  131.     [simfecha] [datetime] NULL,
  132.     [simusuario] [CHAR](10) NULL,
  133.     [sus_impcomicont_mn] [NUMERIC](9, 2) NOT NULL,
  134.     [sus_impcomici_mn] [NUMERIC](9, 2) NOT NULL,
  135.     [sus_codigoant] [CHAR](19) NOT NULL

YY tengo Esta consulta

Código SQL: 
Ver original
  1. SELECT  vemsuscripcion.sus_aten,vemsuscripcion.coa_razsoc,
  2. vemsuscripcion.sus_codigo,vemsuscripcion.sus_finweb
  3. ,vemsuscripcion.coa_direc_ent,vemsuscripcion.dist_cod_ent,vemsuscripcion.coa_razsoc_vend,
  4. simdistritos.dist_nom,vemsuscripcion.art_nom,vemsuscripcion.sus_est  FROM 
  5. vemsuscripcion 
  6. INNER JOIN simdistritos
  7. ON vemsuscripcion.dist_cod_ent=simdistritos.dist_cod 
  8. WHERE 
  9. vemsuscripcion.sus_camp='2012' 
  10. AND art_nom NOT LIKE'%GUBERNAMENTAL%'  AND  sus_est IN ('A', 'E') 
  11.  
  12. --GROUP BY  sus_aten 
  13. ORDER BY sus_aten

cuando descomento --GROUP BY sus_aten
me sale error

Aqui te dejo la base de datos para que lo importes
http://www.megaupload.com/?d=DNVN72OX
esta en sigem_bacup_2012_01.bak
la base de datos se llama
sigem_bacup_2012_01

Si puedes ayudarme seria genial ya que no puedo estar haciando un gropu by por cada registro
Última edición por cesar203; 12/01/2012 a las 17:56