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 2690
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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>...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |