sql - Informix Group por Alias

group-by

Question

¿Qué me estoy perdiendo según esta consulta?

SELECT mymonth, Header1
FROM
(
SELECT month(startdatetime) as mymonth, (CASE WHEN MyTable.somecolumn =2 THEN count(somecolumn2) END) as Header1
FROM MyTable WHERE year(startdatetime)=2013
group by startdatetime 

) x
GROUP BY Header1

Tengo un color rojo en alguna parte que indica que Informix no admite la agrupación por alias, pero cuando intenta establecer GROUP BY 2, también hay un error Column Header1 debe estar en una cláusula Group by

Answer 1

Esa es una sintaxis muy peculiar que tienes en esa consulta. Creo que es la declaración CASE que está causando el problema.

¿Qué es ese logro que no se maneja en esta versión mucho más simple?

SELECT MONTH(startdatetime) AS mymonth, COUNT(*) AS header1
  FROM MyTable
  WHERE YEAR(startdatetime) = 2013
    AND somecolumn = 2
GROUP BY 1

Si estás tratando de hacer algo más funky que lo que has puesto en tu muestra con esa declaración CASE , entonces te recomiendo intentar algo como esto:

SELECT ...., SUM(DECODE(somecolumn, 2, 1, 0))

... pero usualmente ese estilo de sintaxis se usa cuando haces múltiples SUM()s con predicados variables.

ACTUALIZAR

Donde hay múltiples predicados para contar, me gusta hacerlo de esta manera:

SELECT ....
    SUM(CASE WHEN col1 = 1 THEN 1 ELSE 0 END) AS count1,
    SUM(CASE WHEN col3 = 7 AND col5 = 0 THEN 1
             WHEN col3 = 5 AND col5 = 1 THEN 1
             ELSE 0 END) AS count2
  FROM ....

Eso le da mucha flexibilidad para contar muchas cosas diferentes en 1 declaración SQL, siempre y cuando se administren GROUP la misma manera.

Answer 2

Su SQL es muy inusual. El servidor de Informix está confundido acerca de si la expresión CASE es un agregado o no, y yo también. Tal como está escrito, será mejor que reescriba la consulta como:

SELECT mymonth, Header1
  FROM (SELECT MONTH(startdatetime) AS mymonth,
               COUNT(somecolumn2)   AS Header1
          FROM MyTable
         WHERE YEAR(startdatetime) = 2013
           AND SomeColumn = 2
         GROUP BY startdatetime
       ) AS x
-- GROUP BY Header1;
;

No hay ninguna razón para la cláusula GROUP BY de nivel externo (comentada) que puedo ver. Teniendo en cuenta los siguientes datos de prueba:

CREATE TEMP TABLE MyTable
(
    startdatetime   DATE NOT NULL,
    somecolumn      INTEGER NOT NULL,
    somecolumn2     VARCHAR(10)
);

INSERT INTO MyTable VALUES(''2013-03-01'', 2, NULL);
INSERT INTO MyTable VALUES(''2013-03-02'', 2, ''Elephant'');
INSERT INTO MyTable VALUES(''2013-03-03'', 2, ''Rhinoceros'');
INSERT INTO MyTable VALUES(''2013-03-04'', 1, ''Elephant'');
INSERT INTO MyTable VALUES(''2013-03-05'', 3, ''Rhinoceros'');

El resultado de la consulta es:

mymonth      header1
SMALLINT     DECIMAL(15,0)
     3               0
     3               1
     3               1

Sin embargo, sospecho que ha realizado alguna minimización de consultas para ilustrar el problema (de ser así, gracias), y de hecho su sub consulta principal tendría unas pocas expresiones CASE similares, no solo una. En ese caso, debe volver a escribir la expresión CASE y la agregación en estas líneas:

SELECT mymonth, Header1
  FROM (SELECT MONTH(startdatetime) AS mymonth,
               COUNT(CASE WHEN MyTable.somecolumn = 2 THEN somecolumn2 END) AS Header1
          FROM MyTable
         WHERE YEAR(startdatetime) = 2013
         GROUP BY mymonth 
       ) AS x
;

Para los mismos datos de muestra, esto produce:

mymonth     header1
SMALLINT    DECIMAL(15,0)
     3               2

Dado que está utilizando IBM Informix 11.50 en lugar de 11.70 o 12.10, puede que tenga que usar una variación para completar la agregación:

SELECT MonthNum, COUNT(Header1) AS Header1
  FROM (SELECT MONTH(startdatetime) AS MonthNum,
               CASE WHEN MyTable.somecolumn = 2 THEN somecolumn2 END AS Header1
          FROM MyTable
         WHERE YEAR(startdatetime) = 2013
       ) x
 GROUP BY MonthNum;

Salida:

monthnum    header1
SMALLINT    DECIMAL(15,0)
     3               2

La idea básica es usar la expresión CASE para generar los valores que desee en la columna Header1 de la subconsulta, y luego aplicar los agregados a los resultados de la subconsulta (en lugar de agregarlos en la subconsulta). No he verificado que esto funcione en 11.50 (lo hace en 11.70.FC6), pero hay buenas posibilidades de que así sea.