471,071 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

Group by returning multiple data

aspamit
38
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.
Jan 13 '07 #1
2 2643
almaz
168 Expert 100+
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):
Expand|Select|Wrap|Line Numbers
  1. select  b.head_code, 
  2.         max(B.DESCR1), 
  3.         max(C.ECC_NAME),
  4.         max(A.MONTH_ID),
  5.         max(A.FINAN) CIAL_YEAR_ID,
  6.         max(b.start_ceth), 
  7.  
  8.         sum(a.pla_basic) as up_basic,
  9.         sum(A.PLA_EDUCESS) as up_EDUCESS, 
  10.         sum(A.PLA_ADDLGSI) as up_ADDLGSI,
  11.         sum(A.PLA_ADDLTT) as up_ADDLDUTYTANDT, 
  12.         SUM(A.PLA_OTHERS) AS up_SAED,
  13.         sum(A.PLA_CESS) as up_CESS, 
  14.         sum(A.PLA_SED) as up_SED,
  15.         sum(A.PLA_NCCD) as up_NCCD,
  16.         'first' as Type
  17. from    data_table as a inner join ceth_master b
  18.             on a.ceth_id = b.ceth_id inner join ECC_MASTER as C
  19.             on A.ECC_CODE = C.ECC_CODE
  20. where   b.head_code in (
  21.         select  b.head_code
  22.         from    data_table as a left outer join ceth_master as b
  23.                     on a.ceth_id = b.ceth_id
  24.         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)
  25. group by b.head_code
Jan 15 '07 #2
aspamit
38
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
Jan 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by cpisz | last post: by
4 posts views Thread by Daniel | last post: by
13 posts views Thread by Karl Groves | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.