• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

SQL - ORDER BY SUM()

I use delphi with interbase5.
I found out that it is inpossible to do a query like this:

select A.name, sum(B.value) from A,B
where A.name=B.name
group by A.name
order by sum(B.value)

You can't "order by sum()"

In Microsoft accces this is (via ODBC) very easy.

Does anyone has a solution for this or a workaround ?
0
RichardBorst
Asked:
RichardBorst
1 Solution
 
simonetCommented:
Create a VIEW that contains the first select:

CREATE VIEW MyFirstView AS
  select A.name as Name, sum(B.value) as SumOfB from A,B
  where A.name=B.name
  group by A.name

(actual syntax may vary, since I am using SQL Server)

Now, in order for you to select everything sorting by the aggregate field, treat the VIEW as a regular table (all data is dynamic):

SELECT * FROM MyFirstView
ORDER BY SumOfB


Another option is to have a select inside another select (pretty much as a view), but I don't know if Interbase supports it. Anyway, it's like this:

SELECT Name, SumOfB FROM
   (select A.name as Name, sum(B.value) as SumOfB from A,B
  where A.name=B.name
  group by A.name
)
ORDER BY SumOfB


This doesn't work in all cases, but there's a pretty good chance IB supports it. Anyhow, the first approach (creating a VIEW) is still your best shot.

Yours,

Alex



0
 
RichardBorstAuthor Commented:
Sorry, But both your solutions are not supported in interbase.

1.In a view I can't use a 'group by'
and
2.I can't 'select from (select ...'

I think that is a limitation of interbase.

The only 'solution' I found out is  to create a second table based on the query with the 'group by' and then open the table and with an order by the sum-field.

Unfortunalely this is a time-consuming operation.
0
 
johnstonedCommented:
I'm not sure about interbase 5, but this statement works in SQL Server7,

SELECT name, SUM(value) AS Total
FROM b
WHERE name IN
        (SELECT DISTINCT name
      FROM a)
GROUP BY name
ORDER BY Total


Dave.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RichardBorstAuthor Commented:
sorry johnstoned but order by total does not work ininterbase
0
 
DValeryCommented:
Hi, RichardBorst

It's easy :o)

select A.name, sum(B.value)
from A,B
where A.name=B.name
group by A.name
order by 2
0
 
RichardBorstAuthor Commented:
sorry for the delay.

Your solution works !
In fact, it's so simple, but I could not find this in any helpfile or book.

Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now