I need to run a query in an Access client to my Postgres backend.
This is the query:
SELECT DISTINCT projectId, sum(billableHours), sum(billableHours *
billingRate)
FROM time_card_hours
GROUP BY projectId
When I run this in psql it says I need to cast to make the multiplication
work.
So I do this:
SELECT DISTINCT projectId, sum(billableHours), sum(billableHours *
cast(billingRate as double precision))
FROM time_card_hours
GROUP BY projectId
or:
SELECT DISTINCT projectId, sum(billableHours), sum(cast(billableHours as
numeric) * billingRate)
FROM time_card_hours
GROUP BY projectId
Both of which work fine, the first rounding and the second showing the
decimal places in the second sum.
Now to my question. When I do this from within Access it wants to use it's
own SUM and so it complains about the cast(). How can I either force Access
into sending the entire query to Postgres and just waiting for the
resultset, or force Access into performing the multiplication also?
Or my other thought is that I need to build my own operator/function to
handle the multiplying of double precision and numeric data types. If this
is what I should do how should I go about it?
Thanks,
Rob
