Top 10 records and sum of all the others

[n82109]

Hi All,

I am working on a report wherein I need to extract top 10 clients based on the Amounts Paid and in the eleventh row I need to see all the rest of the clients clubbed as 'Others'.
Something like this :
Client Name Total Paid
Client A 100
Client B 90
Client C 80
Client D 70
Client E 60
Client F 50
Client G 40
Client H 30
Client I 20
Client J 10
OTHERS 100

I have drafted a query to fetch this data following this structure TOP 10 query
Union
(ALL Query <> TOP 10 query)

The detailed query looks like this ..

Select Top 10 Client."Name" AS Client_Name ,
Year(PTPReceivables."DateOfService")||'/'||Month(PTPReceivables."DateOfService"),
sum(PTPReceivables."TotalPaid") AS TotalPaid
FROM "AR"."Client" Client INNER JOIN "AR"."PTPReceivables" PTPReceivables ON Client."ClientId" = PTPReceivables."ClientId"
Where (PTPReceivables."DateOfService") between $P{startDate} and $P{endDate}
Group by PTPReceivables."ClientId"
Order by Totalpaid DESC
Union
SELECT
Client."Name" as Others,
Year(PTPReceivables."DateOfService")||'/'||Month(PTPReceivables."DateOfService"),
sum(PTPReceivables."TotalPaid") AS Totalpaid
FROM
"AR"."Client" Client INNER JOIN "AR"."PTPReceivables" PTPReceivables ON Client."ClientId" = PTPReceivables."ClientId"
Where PTPReceivables."ClientId" NOT IN( Select Top 10 sum(PTPReceivables."TotalPaid") AS TotalPaid
FROM "AR"."Client" Client INNER JOIN "AR"."PTPReceivables" PTPReceivables ON Client."ClientId" = PTPReceivables."ClientId"
where (PTPReceivables."DateOfService") between $P{startDate} and $P{endDate}
Group by PTPReceivables."ClientId"
Order by Totalpaid Desc
) and (PTPReceivables."DateOfService") between $P{startDate} and $P{endDate}
Group by PTPReceivables."ClientId"

Now the problem is that though it fetches the records but it does not club 'Others' in one single row.

Can you help me in finding the issue?Thanks..

[Gil-Galad]

In the second part of the union try:
"Others" as Client_Name,....

[n82109]

Thanks for the reply. I had tried this but the problem is that ireport SQL gives errors for doing so.


Error code 29, SQL state S1000: [SQLCODE: <-29>:<Field not found in the applicable tables>]
[Location: <Prepare>]
[%msg: < Field 'OTHERS' not found in the applicable tables^SELECT Others as>]

I wonder why is ireport sql any different.

Regards.