472,791 Members | 978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 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 2690
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

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

Similar topics

6
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values instead of returning a recordset. CREATE PROCEDURE...
9
by: Rowland Hills | last post by:
I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM...
4
by: cpisz | last post by:
At least that is what I think I want to do. What is the proper way for me to return multiple data member objects from an accessor method in my class while ensuring the data does not get changed,...
1
by: Jim P. | last post by:
I'm having trouble returning an object from an AsyncCallback called inside a threaded infinite loop. I'm working on a Peer2Peer app that uses an AsyncCallback to rerieve the data from the remote...
4
by: Daniel | last post by:
I've been asking around and reading but I cannot find a definitive answer. I have customers that need information from our calendar application. The data will come from SQL Server 2000. The...
1
by: durban | last post by:
I am trying to return a single referance to a each unique master record (FILM_PROD_GUDE.RECNO). Because I am returning fields with different values from related tables (FILM_SUBCAT_REF.SUBNO), I am...
13
by: Karl Groves | last post by:
I'm missing something very obvious, but it is getting late and I've stared at it too long. TIA for responses I am writing a basic function (listed at the bottom of this post) that returns...
1
by: n.phelge | last post by:
I'm using XSLT and .NET Framework 1.1 to try to transform XML to group by multiple values, and I'm not succeeding. I have source XML that looks like the following: <Data> <Flavor>...
5
natalie99
by: natalie99 | last post by:
Hi everyone I have a small problem, which is making it impossible to acheive my db aims! I would like to know how to write a query that will tell me if a field item is in IN another table,...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.