-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Gordon Niessen
Sent: 8. juni 2011 17:22
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Aggregating a Union

On 6/8/2011 10:08 AM, Gordon Niessen wrote:
>
> I am using FB1.5 and am trying to do a GROUP BY on the result of a
> UNION, but some how the syntax is eluding me.
>
> SELECT IC.item_id, IC.item_desc, SUM(IC1.qty), SUM(IC1.cost),
> SUM(IC1.ext_cost)
> FROM (
> SELECT IC1.item_id, IC1.item_desc, -IC1.qty, -IC1.cost, -IC1.ext_cost
> FROM ItemCost(12345) IC1
> UNION
> SELECT IC2.item_id, IC2.item_desc, IC1.qty, IC1.cost, IC1.ext_cost
> FROM ItemCost(12350) IC2
> ) IC
> GROUP BY IC.item_id, IC.item_desc
>
> Do I have to embed the union set in a Stored Procedure to later do the
> GROUP BY? I can't use a CASE on the qty, cost, and ext_cost by Order_ID
> as the actual ItemCost is actually a Stored Procedure call and it does
> not return Order_ID.
>
> Or is this not possible in FB1.5? How about FB2.5?
>
>I have confirmed it works in 2.5, but I need a solution for FB1.5 for a
>number of customers.

I don't even think SELECT ... FROM (SELECT ...) is supported in Fb 1.5. So if you don't use a stored procedure, you might need to do things quite a bit more cumbersome (I assume ItemCost is a simple selectable stored procedure that doesn't update/insert/delete anything):

SELECT IC1.item_id, IC1.item_desc,
coalesce((select SUM(IC2A.qty)
FROM ItemCost(12350) IC2A
WHERE IC1.item_id = IC2A.item_id
AND IC1.item_desc = IC2A.item_desc), 0) -
(select SUM(IC1A.qty)
FROM ItemCost(12345) IC1A
WHERE IC1.item_id = IC1A.item_id
AND IC1.item_desc = IC1A.item_desc) as qty,
coalesce((select SUM(IC2A.cost)
FROM ItemCost(12350) IC2A
WHERE IC1.item_id = IC2A.item_id
AND IC1.item_desc = IC2A.item_desc), 0) -
(select SUM(IC1A.cost)
FROM ItemCost(12345) IC1A
WHERE IC1.item_id = IC1A.item_id
AND IC1.item_desc = IC1A.item_desc) as cost,
coalesce((select SUM(IC2A.ext_cost)
FROM ItemCost(12350) IC2A
WHERE IC1.item_id = IC2A.item_id
AND IC1.item_desc = IC2A.item_desc), 0) -
(select SUM(IC1A.ext_cost)
FROM ItemCost(12345) IC1A
WHERE IC1.item_id = IC1A.item_id
AND IC1.item_desc = IC1A.item_desc) as ext_cost
FROM ItemCost(12345) IC1
UNION
SELECT IC2.item_id, IC2.item_desc,
(select SUM(IC2A.qty)
FROM ItemCost(12350) IC2A
WHERE IC2.item_id = IC2A.item_id
AND IC2.item_desc = IC2A.item_desc),
(select SUM(IC2A.cost)
FROM ItemCost(12350) IC2A
WHERE IC2.item_id = IC2A.item_id
AND IC2.item_desc = IC2A.item_desc),
(select SUM(IC2A.ext_cost)
FROM ItemCost(12350) IC2A
WHERE IC2.item_id = IC2A.item_id
AND IC2.item_desc = IC2A.item_desc)
FROM ItemCost(12350) IC2
WHERE NOT EXISTS(SELECT * FROM ItemCost(12345) ICN1
WHERE IC2.item_id = ICN1.item_id
AND IC2.item_desc = ICN1.item_desc)

HTH,
Set