RichardBorst
asked on
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 ?
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 ?
ASKER
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.
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.
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.
SELECT name, SUM(value) AS Total
FROM b
WHERE name IN
(SELECT DISTINCT name
FROM a)
GROUP BY name
ORDER BY Total
Dave.
ASKER
sorry johnstoned but order by total does not work ininterbase
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Your solution works !
In fact, it's so simple, but I could not find this in any helpfile or book.
Thanks.
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