Group by returning multiple data

[aspamit]

I am writing a query like this


select b.head_code,
sum(a.pla_basic ) as up_basic,sum(A. PLA_EDUCESS) as
up_EDUCESS,sum( A.PLA_ADDLGSI) as up_ADDLGSI,sum( A.PLA_ADDLTT)
as up_ADDLDUTYTAND T, SUM(A.PLA_OTHER S) AS up_SAED,sum(A.P LA_CESS) as
up_CESS, sum(A.PLA_SED) as up_SED,sum(A.PL A_NCCD)
as up_NCCD from data_table as a inner join ceth_master b on
a.ceth_id=b.cet h_id inner join ECC_MASTER as C
on A.ECC_CODE=C.EC C_CODE where
b.head_code in (select b.head_code from data_table as a left outer join
ceth_master as b on a.ceth_id=b.cet h_id where a.ceth_no between b.start_ceth
and b.end_ceth and a.month_id=7 and a.financial_yea r_id=5)
group by b.head_code

Here suppose two head_code are same then I am calculating their data.
Upto this it is fine.
Now I want to add some more columns in my query so I write like this.

select b.head_code,B.D ESCR1,C.ECC_NAM E,A.MONTH_ID,A. FINANCIAL_YEAR_ ID,b.start_ceth ,
sum(a.pla_basic ) as up_basic,sum(A. PLA_EDUCESS) as up_EDUCESS,sum( A.PLA_ADDLGSI) as up_ADDLGSI,sum( A.PLA_ADDLTT)
as up_ADDLDUTYTAND T, SUM(A.PLA_OTHER S) AS up_SAED,sum(A.P LA_CESS) as up_CESS, sum(A.PLA_SED) as up_SED,sum(A.PL A_NCCD)
as up_NCCD,'first' as Type from data_table as a inner join ceth_master b on a.ceth_id=b.cet h_id inner join ECC_MASTER as C
on A.ECC_CODE=C.EC C_CODE where
b.head_code in (select b.head_code from data_table as a left outer join
ceth_master as b on a.ceth_id=b.cet h_id where a.ceth_no between b.start_ceth and b.end_ceth
AND A.DELETE_STATUS ='N' and a.month_id = 7 and a.financial_yea r_id = 5 AND A.ECC_CODE = 130801)
group by b.head_code,b.d escr1,c.ecc_nam e,A.MONTH_ID,A. FINANCIAL_YEAR_ ID,b.start_ceth


but this time I am not getting the result as expected.The Head_code is repeating.
Any help is appreciated.
It's urgent .
Thank u.

[almaz]

Please describe what results you expect to get plus sample data.

In your second query you are grouping by fields from all 3 tables, so it is natural that you get duplicate head_code values, because there are different data_table.MONT H_ID for the same head_code.
If you want to get unique head_code values, than you have to use grouping only by fields from ceth_master table, and apply aggregate function on all other selected fields.
Something like this (I use MAX for all fields, it may have no sense for your application, so you'll have to replace them with more meaningful functions):

select  b.head_code, 
		max(B.DESCR1), 
		max(C.ECC_NAME),
		max(A.MONTH_ID),
		max(A.FINAN) CIAL_YEAR_ID,
		max(b.start_ceth), 

		sum(a.pla_basic) as up_basic,
		sum(A.PLA_EDUCESS) as up_EDUCESS, 
		sum(A.PLA_ADDLGSI) as up_ADDLGSI,
		sum(A.PLA_ADDLTT) as up_ADDLDUTYTANDT, 
		SUM(A.PLA_OTHERS) AS up_SAED,
		sum(A.PLA_CESS) as up_CESS, 
		sum(A.PLA_SED) as up_SED,
		sum(A.PLA_NCCD) as up_NCCD,
		'first' as Type
from    data_table as a inner join ceth_master b
            on a.ceth_id = b.ceth_id inner join ECC_MASTER as C
            on A.ECC_CODE = C.ECC_CODE
where   b.head_code in (
        select  b.head_code
        from    data_table as a left outer join ceth_master as b
                    on a.ceth_id = b.ceth_id
        where   a.ceth_no between b.start_ceth and b.end_ceth AND A.DELETE_STATUS = 'N' and a.month_id = 7 and a.financial_year_id = 5 AND A.ECC_CODE = 130801)
group by b.head_code

[aspamit]

Thanks almaz.
My problem is solved.I have altered the query like this,



select 'FIRST' AS TYPE,b.head_cod e,max(b.start_c eth)AS START_CETH,max( b.end_ceth)AS END_CETH, B.DESCR1,A.MONT H_ID,A.FINANCIA L_YEAR_ID,C.ECC _NAME,
sum(a.pla_basic ) as basic,sum(A.PLA _EDUCESS) as EDUCESS,sum(A.P LA_ADDLGSI) as ADDLGSI,sum(A.P LA_ADDLTT)
as ADDLDUTYTANDT, SUM(A.PLA_OTHER S) AS SAED,sum(A.PLA_ CESS) as CESS, sum(A.PLA_SED) as SED,sum(A.PLA_N CCD)
as NCCD from data_table as a inner join ceth_master b on a.ceth_id=b.cet h_id inner join ECC_MASTER as C
on A.ECC_CODE=C.EC C_CODE where
b.head_code in (select b.head_code from data_table as a left outer join
ceth_master as b on a.ceth_id=b.cet h_id where a.ceth_no between b.start_ceth and b.end_ceth)
and a.month_id=7 and a.financial_yea r_id=5 and a.delete_status ='N' and a.ecc_code=1308 01
group by b.head_code,B.D ESCR1,A.MONTH_I D,A.FINANCIAL_Y EAR_ID,C.ECC_NA ME
union
select 'SECOND' AS TYPE, b.head_code,max (b.start_ceth)A S START_CETH,max( b.end_ceth)AS END_CETH,B.DESC R1,C.SR_NO,A.FI NANCIAL_YEAR_ID ,C.ECC_NAME,
sum(a.pla_basic ) as up_basic,sum(A. PLA_EDUCESS) as up_EDUCESS,sum( A.PLA_ADDLGSI) as up_ADDLGSI,sum( A.PLA_ADDLTT)
as up_ADDLDUTYTAND T, SUM(A.PLA_OTHER S) AS up_SAED,sum(A.P LA_CESS) as up_CESS, sum(A.PLA_SED) as up_SED,sum(A.PL A_NCCD)
as up_NCCD from data_table as a inner join ceth_master b on a.ceth_id=b.cet h_id inner join ECC_MASTER as C
on A.ECC_CODE=C.EC C_CODE where
b.head_code in (select b.head_code from data_table as a left outer join
ceth_master as b on a.ceth_id=b.cet h_id where A.DELETE_STATUS ='N' and a.ceth_no between b.start_ceth and b.end_ceth )
and a.month_id between 1 and 6 and a.financial_yea r_id=5 and a.delete_status ='N' and a.ecc_code=1308 01
group by b.head_code, B.DESCR1,C.SR_N O,A.FINANCIAL_Y EAR_ID,C.ECC_NA ME