• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

Strange Group By error

Hi guys
I have a very strange error
This query works:

SELECT A.ID AS  id,
       sum(case                      
        WHEN T.BILLID = 'ABC' THEN T.BILLAMT
       END) AS nightbill,                    
       sum(case                              
        WHEN T.BILLID = 'Off' THEN T.BILLAMT
       END) AS offbill,                      
       sum(CASE                              
        WHEN T.BILLID = 'Peak' THEN T.BILLAMT
       END) AS peakbill                
  FROM  STOCKPLANNIG    A
        INNER JOIN STOCKVERSIONS  B
        ON B.ID = A.ID
        INNER JOIN STOCKAMT T
        ON T.ID = A.ID
 WHERE A.IND    = 'r'
AND A.ID = 1
GROUP BY A.ID      
ORDER BY A.ID
               OPTIMIZE FOR 1 ROW
                FETCH FIRST 201 ROWS ONLY
                WITH UR    

i am just adding two more columns A.DESC and A.MRKID to the select statement and the query stops working!
this is what i do:

SELECT A.ID AS  id,
       A.DESC  AS Desc,  //added new one
       A.MRKID  AS MRKID, //added new one. rest of query is same
       sum(case                      
        WHEN T.BILLID = 'ABC' THEN T.BILLAMT
       END) AS nightbill,                    
       sum(case                              
        WHEN T.BILLID = 'Off' THEN T.BILLAMT
       END) AS offbill,                      
       sum(CASE                              
        WHEN T.BILLID = 'Peak' THEN T.BILLAMT
       END) AS peakbill                
  FROM  STOCKPLANNIG    A
        INNER JOIN STOCKVERSIONS  B
        ON B.ID = A.ID
        INNER JOIN STOCKAMT T
        ON T.ID = A.ID
 WHERE A.IND    = 'r'
AND A.ID = 1
GROUP BY A.ID      
ORDER BY A.ID
                OPTIMIZE FOR 1 ROW
                FETCH FIRST 201 ROWS ONLY
                WITH UR    

All i am doing in the second one is adding additional fields:
A.DESC  AS Desc    and    A.MRKID  AS MRKID   and rest of query is same
but it throws an error saying:

 [SELECT - 0 row(s), 0.032 secs]  [Error Code: -122, SQL State: 42803]  [IBM][CLI Driver][DB2] SQL0122N  A SELECT statement with no GROUP BY clause contains a column name and a column function in the SELECT clause, or a column name is contained in the SELECT clause but not in the GROUP BY clause.  SQLSTATE=42803

Any ideas why this strange error is happening??  

thanks
J
0
jaggernat
Asked:
jaggernat
  • 8
  • 6
2 Solutions
 
DiscoNovaCommented:
GROUP BY combines a lot of rows into "one". Thus you can not select a column from the set of rows (which one would you want; you need to handle them through aggregate functions like MIN, MAX, SUM, AVG, etc.) unless it is one of the fields listed in the GROUP BY -clause (because the column's content will be the same an all rows of the grouped set). However... you should be able to do something like this (I must warn, that DB2's syntax is not really familiar to me):

SELECT A.ID AS  id,
       min(case                      
        WHEN A.DESC IS NOT NULL THEN A.DESC
       END)  AS DESC,
       min(case                      
        WHEN A.MRKID IS NOT NULL THEN A.MRKID
       END)  AS MRKID,
       sum(case                      
        WHEN T.BILLID = 'ABC' THEN T.BILLAMT
       END) AS nightbill,                    
       sum(case                              
        WHEN T.BILLID = 'Off' THEN T.BILLAMT
       END) AS offbill,                      
       sum(CASE                              
        WHEN T.BILLID = 'Peak' THEN T.BILLAMT
       END) AS peakbill                
  FROM  STOCKPLANNIG    A
        INNER JOIN STOCKVERSIONS  B
        ON B.ID = A.ID
        INNER JOIN STOCKAMT T
        ON T.ID = A.ID
 WHERE A.IND    = 'r'
AND A.ID = 1
GROUP BY A.ID      
ORDER BY A.ID
                OPTIMIZE FOR 1 ROW
                FETCH FIRST 201 ROWS ONLY
                WITH UR
0
 
jaggernatAuthor Commented:
thanks for responding.
>>> min(case                      
        WHEN A.DESC IS NOT NULL THEN A.DESC
       END)  AS DESC

can you plz tell me what 'min' does in above statement.  
0
 
DiscoNovaCommented:
MIN is an aggregate function that returns the "item with the smallest identifier" of the group. For strings, this usually means the shortest one of them, though I'm not entirely sure of how DB2 implements it.

Basically, it is just used to get one value out of the group of rows (because you're not supposed to get more or the query fails). Note however, that you have no neat way to get all the values you might want (if, for example, each of the lines contains a different description).

You could possibly do something more appropriate (than the MIN/MAX) using the method outlined in http://www.ibm.com/developerworks/db2/library/techarticle/0309stolze/0309stolze.html
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
tliottaCommented:
jaggernat:

It provides a way for the database to choose which one of the possible DESCs to choose within a group of IDs.

The database assumes that there will be different values in the rows in a 'normalized' database. A GROUP BY ID clause will give a _single_ result row for each unique ID. Since you didn't say "GROUP BY ID, DESC", the database must assume that a set of 2, 3 or 100 rows that all contain the same value for ID can each have a different value for DESC. E.g.:

ID    DESC
---    ------------
A01  White
A01  Green
A01  Blue
A01  Red

Now, when a summary result row is calculated for all of the rows where ID='A01', which value should be provided in it for DESC?

The use of MIN() tells the database to use whichever value is the minimum in that set. You could use a different function such as MAX().

The alternatives are to include DESC in the GROUP BY and to remove DESC from the columns in the SELECT.

One way or another, if you're expecting it to be in the result columns, you _have_ to tell it how to make the choice of which value to put there. Note that if they happen to be the same for every ID='A01', then MIN() simply results in any one of those same values.

Tom
0
 
DiscoNovaCommented:
To sum (pun intended) it up, the answer to your actual question "Any ideas why this strange error is happening??" would be "that happens because in a GROUP BY-query, you can only use either the columns listed in the GROUP BY-clause, or columns passed through aggregate functions".
0
 
DiscoNovaCommented:
To further expand what tliotta commented; if you know that each of the rows grouped will contain the same value, you could add that column name into the group by -clause, and you wouldn't need to use the aggregate function at all. But as said, this is only supposing the column value will be the same (and if it is, your database might need some further thinking:)
0
 
jaggernatAuthor Commented:
thanks guys
>>>>if you know that each of the rows grouped will contain the same value, you could add that column name into the group by -clause.

ok, great. In my case Id and Desc do have the same value but for each Id ,the nightbill,offbill and peakbill values differ. My rows are something like this
Id     Desc     nightbill             offbill                 peakbill
1        abc        01                    00                       03
1        abc        00                    02                      00


so does my query become:

SELECT A.ID AS  id,
       sum(case                      
        WHEN T.BILLID = 'ABC' THEN T.BILLAMT
       END) AS nightbill,                    
       sum(case                              
        WHEN T.BILLID = 'Off' THEN T.BILLAMT
       END) AS offbill,                      
       sum(CASE                              
        WHEN T.BILLID = 'Peak' THEN T.BILLAMT
       END) AS peakbill                
  FROM  STOCKPLANNIG    A
        INNER JOIN STOCKVERSIONS  B
        ON B.ID = A.ID
        INNER JOIN STOCKAMT T
        ON T.ID = A.ID
 WHERE A.IND    = 'r'
AND A.ID = 1
GROUP BY A.ID,
GROUP BY A.DESC  AS Desc,           //put them here
GROUP BY  A.MRKID  AS MRKID
ORDER BY A.ID
               OPTIMIZE FOR 1 ROW
                FETCH FIRST 201 ROWS ONLY
                WITH UR    

thnks very much for the help
0
 
DiscoNovaCommented:
Instead of...

GROUP BY A.ID,
GROUP BY A.DESC  AS Desc,           //put them here
GROUP BY  A.MRKID  AS MRKID

...just...

GROUP BY A.ID, A.DESC, A.MRKID

...will be required
0
 
jaggernatAuthor Commented:
ok thnks,
 but i am storing the value of  A.ID  in id,   A.DESC in desc and  A.MRKID in mrkid.
so can i say
GROUP BY A.ID AS id, A.DESC  AS Desc,A.MRKID  AS mrkid
Or do i put them in the select statement?

0
 
DiscoNovaCommented:
you give the aliases in the select-clause, the group by-clause has no need for them, quite the contrary :)
0
 
jaggernatAuthor Commented:
ok thanks,
also, i was just thinking,i have like 15 columns in my select clause
so if i put all of them in the GROUP BY clause, something like this  >>>GROUP BY A.ID1, A.DESC2, A.MRKID3, A.4, A.5, A.6, A.7, A.8........A.15th column

will it effect the performance of the query because there are so many columns in the GROUP BY clause?

thanks


0
 
DiscoNovaCommented:
It shouldn't have a (negative) effect on the performance. Databases have been born for the task of finding data, joining tables, grouping and sorting results and storing data (more or less, in this order).

However, if your table has 15 columns that do nothing but repeat the same data for all rows where id=something, those columns should not be in the table in the first place. They should be stored in a separate table, and your query should only join the results of this GROUP BY-query into the other table using the ID as a key. But that really is a whole other issue :)
0
 
jaggernatAuthor Commented:
actually they are in a seperate tables.
Id and Desc are in one table called STOCKPLANNIG  .
nightbill, offbill   are in a seperate table called STOCKAMT.
the business logic is each Id in STOCKPLANNIG   table can have two records in STOCKAMT table,
one has the nightbll amount , and the other record has the offbill amount
these are the rows in STOCKAMT  table:
Id     Desc     nightbill             offbill                
1        abc        01                    00                 //this record has the nightbill amount  
1        abc        00                    02                 //this record has the offbill amount.

so i am doing a sum and getting a single record which has nightbill  and offbill values. and i am joining both tables like this

SELECT A.ID AS  id,
       sum(case                      
        WHEN T.BILLID = 'ABC' THEN T.BILLAMT
       END) AS nightbill,                    
       sum(case                              
        WHEN T.BILLID = 'Off' THEN T.BILLAMT
       END) AS offbill              
       
  FROM  STOCKPLANNIG    A          //joining this with STOCKAMT table below
        INNER JOIN STOCKVERSIONS  B
        ON B.ID = A.ID
        INNER JOIN STOCKAMT T
        ON T.ID = A.ID
 WHERE A.IND    = 'r'
AND A.ID = 1
GROUP BY A.ID      
ORDER BY A.ID
               OPTIMIZE FOR 1 ROW
                FETCH FIRST 201 ROWS ONLY
                WITH UR    

when you say
>>>>>>your query should only join the results of this GROUP BY-query into the other table using the ID as a key,
can you tell me how i can do that based on my above scenario.

thanks v much,
J





0
 
DiscoNovaCommented:
Now we are stretching the limits of my knowledge on DB2 SQL syntax, but it should be something not entirely unlike this:

SELECT
      x1.ID AS id,
      x1.DESC AS desc,
      x1.MRKID AS mrkid,
      x2.nightbill AS nightbill,
      x2.offbill AS offbill
FROM
      STOCKPLANNIG x1
JOIN (
      SELECT
            A.ID AS  id,
            sum(case WHEN T.BILLID = 'ABC' THEN T.BILLAMT END) AS nightbill,                    
            sum(case WHEN T.BILLID = 'Off' THEN T.BILLAMT END) AS offbill              
      FROM
            STOCKPLANNIG    A
      INNER JOIN
            STOCKVERSIONS  B ON B.ID = A.ID
      INNER JOIN
            STOCKAMT T ON T.ID = A.ID
      WHERE
            A.IND    = 'r'
            AND A.ID = 1
      GROUP BY A.ID
) x2 ON x1.ID = x2.id
ORDER BY
      x1.ID;

...as you see, the GROUP BY -query is only joined to (it is introduced as what I tend to call "inline view", and I'm not at all sure if DB2 even supports such things). Anyway, now that the GROUP BY is no longer part of the main query, the outer query is perhaps a bit more "flexible".

Hope this helps.
0
 
jaggernatAuthor Commented:
ok thanks,i wil try that and let you know,

thanks
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now