-
Group by function
Hello I am having a problem with the following query.
PHP Code:
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
Last edited by tamilselvan; 05-09-2005 at 06:27 PM.
-
I don't see a join condition between the two tables: lwc a, ldt b
Have I missed it? Is that intentional?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
I need to use group by because I am using other queries with union function.
Thanks,
-
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:
Code:
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.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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." ?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|