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_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 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.month_id=7 and a.financial_year_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.DESCR1,C.ECC_NAME,A.MONTH_ID,A.FINAN CIAL_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_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,b.descr1,c.ecc_name,A.MONTH_ID,A.FINAN CIAL_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.MONTH_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_code,max(b.start_ceth)AS START_CETH,max(b.end_ceth)AS END_CETH, B.DESCR1,A.MONTH_ID,A.FINANCIAL_YEAR_ID,C.ECC_NAME ,
sum(a.pla_basic) as basic,sum(A.PLA_EDUCESS) as EDUCESS,sum(A.PLA_ADDLGSI) as ADDLGSI,sum(A.PLA_ADDLTT)
as ADDLDUTYTANDT, SUM(A.PLA_OTHERS) AS SAED,sum(A.PLA_CESS) as CESS, sum(A.PLA_SED) as SED,sum(A.PLA_NCCD)
as NCCD 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.month_id=7 and a.financial_year_id=5 and a.delete_status='N' and a.ecc_code=130801
group by b.head_code,B.DESCR1,A.MONTH_ID,A.FINANCIAL_YEAR_I D,C.ECC_NAME
union
select 'SECOND' AS TYPE, b.head_code,max(b.start_ceth)AS START_CETH,max(b.end_ceth)AS END_CETH,B.DESCR1,C.SR_NO,A.FINANCIAL_YEAR_ID,C.EC C_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_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 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.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_year_id=5 and a.delete_status='N' and a.ecc_code=130801
group by b.head_code, B.DESCR1,C.SR_NO,A.FINANCIAL_YEAR_ID,C.ECC_NAME