GROUP BY problem in PostgreSQL

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