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
