[Ur] Possible to have products in SQL aggregates?

[Adam Chlipala]

I'm having trouble seeing the issue here.  Why doesn't the following 
code do what you're asking, in a completely idiomatic SQL-y way?

table a : { AId : int }
table aBJoin : { RefA : int, RefB : int }
table b : { BId : int, RefA : int, Quantity: float }
table c : {Token : int, RefB : int, Quantity: float }

val q : sql_query [] [] _ _ =
     (SELECT a.AId, SUM(c.Quantity * b.Quantity)
      FROM c JOIN b ON c.RefB = b.BId
        JOIN aBJoin ON aBJoin.RefB = b.BId
        JOIN a ON a.AId = aBJoin.RefA
      GROUP BY a.AId)

On 05/31/2013 06:39 PM, Daniel Patterson wrote:
> I have a table structure like:
>
> A: aId
>
> ABJoin: refA, refB
>
> B: bId, refA, quantity: float
>
> C: token, refB, quantity: float
>
> The goal is, for a given token, to give a list of all the relevant As,
> by joining C with B on refB = bId and then joining B and A through the
> ABJoin table. I _want_ to see SUM(C.quantity * B.quantity) - ie the
> total number of the specific A that is relevant for the referenced
> token, but I'm not able to write that product. (I could if I were just
> using SQL)
>
> Is there a way to do this? I'd prefer not to bring everything into
> code and then re-implement the join and aggregate...
>