Skip site navigation (1) Skip section navigation (2)

GROUP BY problem in PostgreSQL

From: "Saseendra Babu" <saseendra(at)erdcitvm(dot)org>
To: <pgsql-patches(at)postgresql(dot)org>
Subject: GROUP BY problem in PostgreSQL
Date: 2004-12-20 11:52:49
Message-ID: 002801c4e68a$6e0e6910$161c10ac@babuks (view raw or flat)
Thread:
Lists: pgsql-patches
  
 

Hello,

 We have got a table DUMMY with following structure; 

      DIST 
     AMT 
     FINE  
     MONTH
     
      TVM  
     1000
     2000
     1
     
      TVM            
     2000
     3000
     1
     
      KLM 
     3000
     4000
     1
     
      KLM 
     4000
     5000
     1
     
      ALP
     6000
     7000
     1
     
      ALP
     7000
     8000
     1
     
      TVM  
     1000
     2000
     2
     
      TVM            
     2000
     3000
     2
     
      KLM 
     3000
     4000
     2
     
      KLM 
     4000
     5000
     2
     
      ALP
     6000
     7000
     2
     
      ALP
     7000
     8000
     2
     
      TVM  
     1000
     2000
     3
     
      TVM            
     2000
     3000
     3
     
      KLM 
     3000
     4000
     3
     
      KLM 
     4000
     5000
     3
     
      ALP
     6000
     7000
     3
     
      ALP
     7000
     8000
     3
     
 

 

 

 

 

 

 

 

 

 

 

 In order to have a matrix query to have month wise ,dist (district) wise sum for multiple rows , in Oracle we have executed the following query , 

SELECT DIST,

SUM(decode(month,01,(payment+fine))) "Jan" ,

SUM(decode(month,02,(payment+fine))) "Feb",

SUM(decode(month,03,(payment+fine)))  "Mar",

SUM(decode(month,04,(payment+fine))) "Apr",

SUM(payment+fine) "Month-Tot"

FROM dummy

GROUP BY  dist

UNION

SELECT 'Total',

SUM(decode(month,01,(payment+fine))) "Jan" ,

SUM(decode(month,02,(payment+fine))) "Feb",

SUM(decode(month,03,(payment+fine)))  "Mar",

SUM(decode(month,04,(payment+fine))) "Apr",

SUM(payment+fine) Total

FROM dummy;  

 The result we got as desired.                                     

      DIST
     Jan
     Feb
     Mar
     Month-Tot
     
      ALP
     28000
     28000
     28000
     84000
     
      KLM
     16000
     16000
     16000
     48000
     
      TVM
     8000
     8000
     8000
     24000
     
      Total
     52000
     52000
     52000
     156000
     

                                     

We wrote the equivalent query in PostgreSQL using CASE (instead of DECODE)  as follows        

 SELECT dist,
CASE WHEN month=1 THEN SUM(payment+fine) END   AS Jan ,
CASE WHEN month=2 THEN SUM(payment+fine) END   AS Feb,
CASE WHEN month=3 THEN SUM(payment+fine) END   As Mar,

SUM(payment+fine) AS Month-Tot

GROUP BY dist
FROM dummy;                  

 

ERROR:  Attribute dummy.month must be GROUPed or used in an aggregate function

 

GROUP BY is always problem in PostgreSQL.

 

Will you please help us to solve the problem.,

Thanking you 

Regards

Saseendra Babu K

CDAC ,Trivandrum 

 

 

 

 



______________________________________
Scanned and protected by Email scanner

pgsql-patches by date

Next:From: Merlin MoncureDate: 2004-12-20 16:44:00
Subject: Re: [PERFORM] scalability issues on win32
Previous:From: Alvaro HerreraDate: 2004-12-20 03:40:36
Subject: Re: LockObject patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group