Informix Group by Alias [英] Informix Group by Alias

问题描述

  SELECT mymonth，Header1 
 FROM 
（ 
作为mymonth选择月（startdatetime），（CASE WHEN MyTable.somecolumn = 2 THEN count（somecolumn2）END）Header1 
 FROM MyTable WHERE year（startdatetime）= 2013 
 by startdatetime 
 
）x 
 GROUP BY Header1 
  

Informix不支持通过别名进行分组，但当试图设置
GROUP BY 2时，也有错误
Column Header1必须位于Group by子句中

解决方案

你的SQL非常不寻常。 Informix服务器对于CASE表达式是否是一个聚合表达式感到困惑 - 我也是如此。正如我写的那样，您最好将查询重写为：

<$ p $ （$ datetimetime）AS mymonth，
COUNT（somecolumn2）AS Header1
FROM MyTable
WHERE YEAR（startdatetime ）= 2013
和SomeColumn = 2
GROUP BY startdatetime
）AS x
- GROUP BY Header1;
;

我没有理由看到（注释掉的）外层GROUP BY子句。给定以下测试数据：

pre $ code 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'）;

查询的输出是：

 mymonth header1 
 SMALLINT DECIMAL（15,0）
 3 0 
 3 1 
 3 1 
  
 
 
  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 
; 
  
对于相同的样本数据，这会产生：
 
 
  mymonth header1 
 SMALLINT DECIMAL（15,0）
 3 2 
  
考虑到您正在使用IBM Informix 11.50而不是11.70或12.10，您可能需要使用其中的变体来完成聚合： 
 
 
  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; 
  
输出：
  monthnum header1 
 SMALLINT DECIMAL（15,0）
 3 2 
  
基本思想是使用CASE表达式在子查询的Header1列中生成所需的值，然后将聚合应用于子查询的结果（而不是在子查询中进行聚合）。我还没有证实，这将在11.50（它在11.70.FC6中）有效，但它有一个很好的机会。
 
What am I missing according to this query:
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
I've red somewhere that Informix is not supporting grouping by alias but when trying to set
GROUP BY 2, there is error too
Column Header1 must be in a Group by clause
解决方案
Your SQL is very unusual.  The Informix server is confused about whether the CASE expression is an aggregate or not — and so am I.  As written, you would do best to rewrite the query as:
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;
;
There's no reason for the (commented out) outer-level GROUP BY clause that I can see.  Given the following test data:
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');
The output of the query is:
mymonth      header1
SMALLINT     DECIMAL(15,0)
     3               0
     3               1
     3               1
However, I suspect you've done some query minimization to illustrate the problem (if so, thank you), and in fact your main sub-query would have a few similar CASE expressions, not just one.  In that case, you should rewrite the CASE expression and aggregation along these lines:
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
;
For the same sample data, this produces:
mymonth     header1
SMALLINT    DECIMAL(15,0)
     3               2
Given that you are using IBM Informix 11.50 instead of 11.70 or 12.10, you may have to use a variation on this to get the aggregation done:
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;
Output:
monthnum    header1
SMALLINT    DECIMAL(15,0)
     3               2
The basic idea is to use the CASE expression to generate the values you want in the Header1 column of the sub-query, and then apply the aggregates to the results of the sub-query (instead of aggregating in the sub-query).  I've not verified that this will work in 11.50 (it does in 11.70.FC6), but there's a decent chance that it will.

                        
这篇关于Informix Group by Alias的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！