Group by function

[kiranmayi]

Hello I am having a problem with the following query.

SELECT  STATE,sum(decode(rep_month,'JANUARY', cnt, null)) "JAN-05",
sum(decode(rep_month,'FEBRUARY', cnt, null)) "FEB-05",
Sum(decode(rep_month,'MARCH', cnt, null)) "MAR-05",
sum(decode(rep_month,'APRIL', cnt, null)) "APR-05",
sum(decode(rep_month,'MAY', cnt, null)) "MAY-05",
sum(decode(rep_month,'JUNE', cnt, null)) "JUN-05",
sum(decode(rep_month,'JULY', cnt, null)) "JUL-05",
sum(decode(rep_month,'AUGUST', cnt, null)) "AUG-05",
sum(decode(rep_month,'SEPTEMBER', cnt, null)) "SEP-05",
sum(decode(rep_month,'OCTOBER', cnt, null)) "OCT-05",
sum(decode(rep_month,'NOVEMBER', cnt, null)) "NOV-05",
sum(decode(rep_month,'DECEMBER', cnt, null)) "DEC-05"
from (select decode(state,'TX','ZDA Target','OK','','MO','',
'KS','','AR','') "STATE",
decode(dep_month,1,'JANUARY',2,'FEBRUARY',3,'MARCH',4,'APRIL',5,'MAY',6,'JUNE',7,'JULY',8,'AUGUST',9,'SEPTEMBER',10,'OCTOBER',11,'NOVEMBER',12,'DECEMBER') rep_month,target_value cnt
from lwc a,ldt b
where DMA_STATE_IND='STATE' and DA_LU_IND='DA' and a.region='TEMP' 
GROUP BY STATE,dep_month,target_value) GROUP BY STATE;


output for the query is:

STATE          JAN-05     FEB-05     MAR-05     APR-05     MAY-05     JUN-05
---------- ---------- ---------- ---------- ---------- ---------- ----------
    JUL-05     AUG-05     SEP-05     OCT-05     NOV-05     DEC-05
---------- ---------- ---------- ---------- ---------- ----------
ZDA Target          0          0         27         55        191        273
       273        327        354        409        409        409

                    0          0        108        220        764       1092
      1092       1308       1416       1636       1636       1636 

I do not want to get the second row in the result i.e.

0 0 108 220 764 1092 1092 1308 1416 1636 1636 1636, with out anything under STATE column. It looks like it did multilply data by 4.

Any ideas?

Thanks,
Kiran

[DaPi]

I don't see a join condition between the two tables: lwc a, ldt b
Have I missed it? Is that intentional?

[DaPi]

Group by function

No, there is no join between the two tables but I did use 2 tables because I need different data from the 2 tables.

Thanks,

You do realise that will generate an initial result set (before grouping) which will contain all possible pairings of rows from the two tables? (Cartesian Product)? That might explain why you're out by a factor of four in one case. I'd start thinking about that before looking any further for errors.


P.S. use the "Post Reply" to keep everything in the same thread.

[kiranmayi]

I need to use group by because I am using other queries with union function.

Thanks,

[DaPi]

Originally posted by kiranmayi
I need to use group by because I am using other queries with union function.

I don't doubt that - but it's only by breaking down ("analysing") your query that you'll fix it. You should start by looking at the result of this:

select decode(state,'TX','ZDA Target','OK','','MO','','KS','','AR','') "STATE",
decode(dep_month,1,'JANUARY',2,'FEBRUARY',3,'MARCH',4,'APRIL',5,'MAY',6,'JUNE',
7,'JULY',8,'AUGUST',9,'SEPTEMBER',10,'OCTOBER',11,'NOVEMBER',12,'DECEMBER') rep_month,
target_value cnt
from lwc a, ldt b
where DMA_STATE_IND='STATE' and DA_LU_IND='DA' and a.region='TEMP'

Put the result in a table (or a spread-sheet) so that you can play with it. If that's not right, the rest can never be.

[DaPi]

DOH ! !

I do not want to get the second row in the result . . . without anything under STATE column

Does this mean you just need to add to the WHERE clause:
AND state = 'TX'
? ? ? ?

Did the presence of the other four states "multilply data by 4." ?