Group by function

Printable View

05-06-2005, 01:55 PM
kiranmayi

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
05-06-2005, 02:17 PM
DaPi

I don't see a join condition between the two tables: lwc a, ldt b
Have I missed it? Is that intentional?
05-06-2005, 02:52 PM
DaPi

Quote:

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.
05-06-2005, 03:31 PM
kiranmayi

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

Thanks,
05-07-2005, 06:04 AM
DaPi

Quote:

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.
05-07-2005, 06:14 AM
DaPi

DOH ! ! :o :o :o

Quote:

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." ?