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 ?
RichardBorstAsked:
Who is Participating?
 
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
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
RichardBorstAuthor Commented:
sorry johnstoned but order by total does not work ininterbase
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.