Hi all. I've had awesome responses here thus far (actually better than most sql forums if you can believe that lol).

Anyway here's the query I'm writing. And I've also included output. The division areas of my query are not working. I'm returning 0's. I think one solution is to drop the counts/sums into a table, and then query that second table, but I feel like that's an unnecessary step and my syntax is off.

Please see the query / output.

SELECT
DATE_TRUNC('month', n.campaign_date)
-- , n.campaign_cell_id
-- , n.legacy_campaign_id
-- , n.components
-- , n.envelope_creative
-- , n.envelope_format
-- , n.offer
-- , n.segment
, COUNT(n.nucleus_business_id) Members
, SUM(n.responded) Responses
, AVG(n.score) score
, COUNT(n.opp_id) applications
, COUNT(n.completed_app_date) completed_applications
, COUNT(n.approval_date) approvals
, SUM(n.closed_won) loans

,SUM(n.responded) / COUNT(n.nucleus_business_id) Response_Rate
,COUNT(n.opp_id) Applications
,COUNT(n.opp_id) / SUM(n.responded) App_Rate
,COUNT(n.completed_app_date) completed_applications
,COUNT(n.completed_app_date) / COUNT(n.opp_id) CA_Rate
,COUNT(n.approval_date) approvals
,COUNT(n.approval_date) / COUNT(n.opp_id) Apps_Approvals
,SUM(n.closed_won) Loans
,SUM(n.closed_won) / COUNT(n.approval_date) Booking_Rate
,SUM(n.closed_won) / SUM(n.responded) Responses_ToClose
,SUM(n.closed_won) / COUNT(n.nucleus_business_id) Mailer_Close
FROM
mining_space.nucleus_camp_memb n

WHERE
n.campaign_date > '2014-12-01'

GROUP BY
1
ORDER BY 1


HERE'S THE OUTPUT

i'll just address the first one. try this out and see what you get. the results might surprise you.



http://www.sqlfiddle.com/#!15/b8c87/2

http://www.postgresql.org/message-id/D0666F34C95145368966E0CDE521A815@GISWKSTN

This post was edited by carteblanche on Apr 8 2015 07:27pm

Those are two good resources. So if I have this right (and being how much of a newbie I am I probably don't)

SUM(n.responded::float) / COUNT(n.nucleus_business_id::float) Response_Rate and the same to the other division situations fixes it? Do I then just multiply by 1.0 to get a percentage or?


SUM(n.responded::float) / COUNT(n.nucleus_business_id::float)*100 Response_Rate


edit ... tried it ..it says Cannot cast type date to double precision.hmmmmmmmmm

This post was edited by noflexzone on Apr 8 2015 07:40pm

that should give you a good hint. if you're casting a date instead of an integer, that means you're casting the column, not the sum/count. so look at your sql and figure out how to change it.

COUNT(n.completed_app_date::float)

This post was edited by carteblanche on Apr 8 2015 07:49pm

That's a good point.

I'm getting values for

COUNT(n.completed_app_date)::float / COUNT(n.otherfield)::float

Now I'll need to double check results in excel.