Click here to Skip to main content
13,447,272 members (35,558 online)
Rate this:
Please Sign up or sign in to vote.
See more:

Anybody can help? I am joining four tables to get the required result. Referring to the first script below, I want to get the summary of Charges which is one of the fields of table1.SUM value should be grouped by month according to the adddate field from table4. How can i change of below script to get the expected result.

Hope anyone can help me. :)

SELECT * FROM Table1 WHERE Table1.evidencenumber IN 
(SELECT DISTINCT table2.evidencenumber FROM table3 rs, table2
WHERE table3.rep_id IN (SELECT DISTINCT rep_id FROM table4 WHERE adddate>=TO_DATE('010109','ddmmyy') 
AND adddate<=TO_DATE('311209','ddmmyy') AND dept='Engineering')
AND table3.evidencenumber=table2.evidencenumber
AND table2.pareantid IS NULL)
Posted 22-May-11 23:38pm
Updated 22-May-11 23:51pm
udayakumarcgvak 12-Oct-11 3:54am
Helo Friend,

Your Sql Code is Not clear to me,So U give Table & Field details and Your Requirement.I think that is good for some one understand..


1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

If I correctly understand the purpose you need only summary by the values of Table1, then you can filter distinct results by simply joining all the subqueries in outer "where" clause and adding the "rowid" marker of the row from corresponding table (this is possible in Oracle), and thus calculating the result in the outer query:

select T.adddate, sum(T.Charges) from
SELECT distinct Table1.rowid, Table1.Charges, table4.adddate FROM
 Table1.evidencenumber = table2.evidencenumber 
 AND table3.rep_id = table4.rep_id
 and table4.adddate>=TO_DATE('010109','ddmmyy') 
 AND table4.adddate<=TO_DATE('311209','ddmmyy')
 AND table4.dept='Engineering'
 AND table3.evidencenumber=table2.evidencenumber
 AND table2.pareantid IS NULL
) T 
group by T.adddate

Still if your SQL engine doesnt have the built-in row marker (like rowid in Oracle) you can add a unique field to the Table1 and use it as a rowid-marker.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01-2016 | 2.8.180318.3 | Last Updated 12 Jul 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100