CODEWITHSUNDEEP
sqlgroup-byinformix
Avicena00
Avicena00

Reputation: 365

Informix Group by Alias

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

Upvotes: 2

Views: 4444

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

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.

Upvotes: 1

RET
RET

Reputation: 9188

That's some very peculiar syntax you've got in that query. I think it's the CASE statement that's causing the issue.

What is that achieving that isn't handled in this much simpler version?

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

If you're trying to do something more funky than what you've put in your sample with that CASE statement, then I'd recommend trying something like this:

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

... but usually that style of syntax gets used when you're doing multiple SUM()s with varying predicates.

UPDATE

Where there are multiple predicates for counting, I like to do it this way:

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 ....

That gives you a lot of flexibility to count a lot of different things in 1 SQL statement, as long as they're being GROUPed the same way.

Upvotes: 3

Related Questions