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.
2 2643
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
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
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
6 posts
views
Thread by Samuel Hon |
last post: by
|
9 posts
views
Thread by Rowland Hills |
last post: by
|
4 posts
views
Thread by cpisz |
last post: by
|
1 post
views
Thread by Jim P. |
last post: by
|
4 posts
views
Thread by Daniel |
last post: by
| |
13 posts
views
Thread by Karl Groves |
last post: by
|
1 post
views
Thread by n.phelge |
last post: by
| | | | | | | | | | | |