SQL Add a user created results row

Question

I have the following query:

SELECT 
num.description,
(num.new_driver_form/denom.form_sent_to) as conversion
FROM
(SELECT
'Common' as description,
count(DISTINCT u1.user_id) as new_driver_form
FROM   
user_tags u1
JOIN   
user_tags u2 USING (user_id)
WHERE  
u1.name = 'sentForm'
AND    
u2.name = 'recForm')num
JOIN
(SELECT
'Common' as description,
count(DISTINCT user_id) as form_sent_to
FROM 
user_tags
WHERE
name = 'sentForm')denom
ON num.description = denom.description

I am receiving an error that "failed to find conversion function from unknown to text", but am not sure what that means.

where is the description coming from? – NoChance Nov 06 '13 at 00:17 — NoChance, Nov 06 '13 at 00:17

score 0 · Answer 1 · answered Nov 06 '13 at 00:18

0

This is the easy way

SELECT
  'cohort' as description,
  count(DISTINCT u1.user_id) as new_driver_form,
  x.field as field
FROM user_tags u1
JOIN user_tags u2 USING (user_id)
JOIN anothertable X ON x.blah = u1.blah
WHERE  
  u1.name = 'sentForm'
  AND    
  u2.name = 'recForm'

If the string is comming from one of the tables something like this would work:

SELECT
  <replace with field name> as description,
  count(DISTINCT u1.user_id) as new_driver_form,
  x.field as field
FROM user_tags u1
JOIN user_tags u2 USING (user_id)
JOIN anothertable X ON x.blah = u1.blah
WHERE  
  u1.name = 'sentForm'
  AND    
  u2.name = 'recForm'

answered Nov 06 '13 at 00:18

Hogan

69,564
10
76
117

i know this is a follow on question, but i did that and tried to join on another query that I also add 'cohort' as description to as well. Is that not possible? – Americo Nov 06 '13 at 00:19
I am creating the "description" in both tables, and the error is that it "failed to find conversion function from unknown to text" – Americo Nov 06 '13 at 00:22
@Stuave - You will have to post the sql, I've no idea why that error – Hogan Nov 06 '13 at 00:22

score 0 · Accepted Answer · edited May 23 '17 at 10:32

You need to add an explicit type cast to avoid the error. 'Common' is just a string literal and Postgres wants you to provide the type it should be cast to.

You will also want to cast the bigint number in num.new_driver_form (count() returns bigint) to numeric (or a lossy floating point type) to get a meaningful result from your divison. You will then be interested in the round() function.

SELECT num.description
     ,(num.new_driver_form::numeric / denom.form_sent_to) AS conversion
FROM  (
   SELECT 'Common'::text AS description
         ,count(DISTINCT u1.user_id) AS new_driver_form
   FROM   user_tags u1
   JOIN   user_tags u2 USING (user_id)
   WHERE  u1.name = 'sentForm'
   AND    u2.name = 'recForm'
   ) num
JOIN   (
   SELECT 'Common'::text AS description
         ,count(DISTINCT user_id) AS form_sent_to
   FROM   user_tags
   WHERE  name = 'sentForm'
   ) denom ON num.description = denom.description

Of course, your JOIN doesn't make a lot of sense to begin with, since the second subquery computes a single row and the JOIN condition is always TRUE. Can be simplified to:

SELECT num.description
     ,(num.new_driver_form::numeric / denom.form_sent_to) AS conversion
FROM  (
   SELECT 'Common'::text AS description
         ,count(DISTINCT u1.user_id) AS new_driver_form
   FROM   user_tags u1
   JOIN   user_tags u2 USING (user_id)
   WHERE  u1.name = 'sentForm'
   AND    u2.name = 'recForm'
   ) num
JOIN   (
   SELECT count(DISTINCT user_id) AS form_sent_to
   FROM   user_tags
   WHERE  name = 'sentForm'
   ) denom ON TRUE

Or just use a subquery:

SELECT 'Common'::text AS description
      ,(count(DISTINCT u1.user_id)::numeric
       / (SELECT count(DISTINCT user_id)
          FROM   user_tags
          WHERE  name = 'sentForm')) AS conversion
FROM   user_tags u1
JOIN   user_tags u2 USING (user_id)
WHERE  u1.name = 'sentForm'
AND    u2.name = 'recForm'

SQL Add a user created results row

2 Answers2