Q:
Why does this query fail in Firebird but work in SQL Server?
I have this query:
select @d from Users where lastname=@lastname and groupname=@groupname
This returns: 0 (in Firebird)
select @d=SUM(amount) from user_details where lastname=@lastname and groupname=@groupname
this returns some value (in SQL server)
Can anyone please explain what's happening?
A:
In SQL Server, you may want to use the OVER (ORDER BY) clause if you want to get the sum total of an ordered value:
SELECT SUM(amount) OVER(ORDER BY groupname ASC) FROM user_details
where ASC is just an arbitrary example, can be DESC or ASC..
If you don't care about the actual ordering of the values in the column, you can simply do:
SELECT SUM(amount) OVER() FROM user_details
This is also the default behavior in Firebird.
Check this: http://firebirdsql.org/file/documentation/reference_manuals/user_reference/fblangref25-sqlref/xqueryfunctions/xaggrow.html
A:
Firebird 2.0 SQL Reference, Release 2.0, 11.22.6 OVER CLAUSE in chapter XQuery Functions says:
GROUP BY OVER (ORDER BY expression ASC|DESC)
GROUP BY clause is applied to the rows ordered by the ORDER BY expression. The first row of the result set is always taken from the first row of the result set of the query.
In other words, you need to apply the ORDER BY to the aggregate function:
SELECT @d = SUM(amount) FROM user_details
WHERE lastname=@lastname AND groupname=@groupname
GROUP BY GROUPNAME;
UPDATE
Firebird 2.5 XQuery Reference:
AFAIK, it is NOT possible to use GROUP BY OVER, but OVER clause is supported:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY groupname DESC) AS RowNumber, * FROM (SELECT * FROM user_details ORDER BY 1)
)
WHERE row_number = 2;
where:
SELECT: result of the SELECT statement.
(SELECT * FROM): result of the anonymous view/subquery.
(SELECT ROW_NUMBER() OVER (ORDER BY groupname DESC) AS RowNumber): ORDER BY clause.
WHERE row_number = 2; SELECT 2 rows from a result set.
P.S.
I don't know what's the difference between Firebird 2.5 and 2.0 SQL Reference, Release 2.0, 11.22.6 OVER CLAUSE
A:
In the Firebird 2.5 version of the docs, the OVER clause supports grouped calculations. This worked for me:
select @d = SUM(amount) from user_details
group by groupname
order by 1 desc;
(Or of course you could do the calculation as part of the select statement instead).
Alternatively, you can try the following:
select @d = SUM(amount)
from user_details
where lastname = @lastname and groupname = @groupname;
I don't know why the above worked for you. I found this other thread where the first method was mentioned and in a comment someone mentions that the following worked for them:
select @d = SUM(amount)
from user_details
where lastname = @lastname;
The above works in 2.0 as well (with the caveat that the sum does not have an ORDER BY clause). However, since the group by clause is after the where clause, I don't know why the sum calculation should be grouped by the grouping function; maybe someone else can chime in and explain why that works.
